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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Create Report Quickly with VBA”

  1. Hello sir,

    Thank you for the video!
    If the same data available in closed workbook then how to write the code and where its need to be change

  2. Good day pls I want to set up excell that can send shedule happy birthday SMS to different users. Kindly assist.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.