Create Report Quickly with VBA

How to create report quickly and easily with VBA. We have learned to create reports in a variety of ways like using loops, auto-filter, advanced filter, Vlookup, etc. Today we learn to create reports quickly and easily which include dates. Watch the video to learn interesting code to solve such problems:

Here’s the complete VBA code to create reports based on dates (month and year):

Sub createquickreport()
Dim itemid As String
Dim i As Long, lastrow As Long, totalamount As Long, qty As Long
Dim mydate1 As Date, mydate2 As Date
Dim strResult1 As String, strResult2 As String

lastrow = Sheets(1).Range(“A” & Rows.Count).End(xlUp).Row
qty = 0
totalamount = 0

itemid = InputBox(“Enter an Item ID”, “Item ID”)
mydate1 = InputBox(“Enter start date”, “Start Date”)
mydate2 = InputBox(“Enter end date”, “End Date”)

Sheet1.Activate

For i = 2 To lastrow

If Cells(i, 1) = itemid And Cells(i, 4) >= mydate1 And Cells(i, 4) <= mydate2 Then
    totalamount = totalamount + (Cells(i, 2) * Cells(i, 3))
    qty = qty + Cells(i, 3)
End If

Next i

strResult1 = MonthName(Month(mydate1))
strResult2 = MonthName(Month(mydate2))
If strResult1 = strResult2 Then
Sheet2.Range(“A1″) = strResult1 & ” ” & Year(mydate1) & ” ” & ” ” & “Purchase Report” & ” for ” & itemid
Else
Sheet2.Range(“A1”) = strResult1 & “-” & strResult2 & ” ” & Year(mydate1) & ” ” & ” ” & “Purchase Report” & ” for ” & itemid
End If
Sheet2.Range(“B2”) = itemid
Sheet2.Range(“B3”) = qty
Sheet2.Range(“B4”) = totalamount
Sheet2.Activate

End Sub