How to Generate Reports in Excel Using VBA


A person working for an advertising company generates leads every day. He enters the data of the leads meticulously every day. Now at the end of the month or week he wishes to generate a report automatically. He also wants to ensure that no blank rows are inserted into the ‘report’ worksheet.

To generate a report automatically in Excel we would first need to clear all the contents of the report sheet.

Next we would copy the data in each of the many sheets except our report sheet.

We then access our ‘report’ worksheet and find the next blank row for data entry. We can also enter the headers in the ‘report’ sheet using VBA code.

Now we paste the copied values into the report sheet from each of the ‘leads’ sheets. Since we access the next blank row every time, we automatically avoid blank rows.

Finally we do a little ‘hose-keeping’ of our report sheet to make our data look good.

Watch the video below and then study the VBA code carefully to see how reports can be generated in Excel automatically using VBA:


Private Sub Workbook_Open()
Dim nextblankrow As Long
Sheets(“Report”).Select
Sheets(“Report”).Cells.ClearContents
For i = 1 To Sheets.Count – 1
Sheets(i).Range(“A2:D50”).Copy
nextblankrow = Sheets(“Report”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets(“Report”).Cells(nextblankrow, 1).PasteSpecial Paste:=xlPasteValues
Next
Cells(1, 5).Select
Application.CutCopyMode = False
Sheets(“Report”).Range(Cells(1, 1), Cells(nextblankrow, 4)).Columns.AutoFit

End Sub

To add headers to the report sheet automatically we can add the following lines of code after clearing the cell contents in the report sheet:

Range(“A1″).Value=”Name”
Range(“B1″).Value=”Mobile”
Range(“C1″).Value=”Phone”
Range(“D1″).Value=”Interested in”

Download sample file by clicking on the Excel icon:

8 thoughts on “How to Generate Reports in Excel Using VBA

  1. help

    i copy the code and paste it (For i = 1 To Sheets.Count – 1,Sheets(i).Range(“A2:D50″).Copy) but his come in red, any things that i mistake?

    Reply
    1. Disney

      yeah, the whole bond focus as well as the Chicago rencnetmeat seemed rather out of place and gave the show more of a disjointed feel. I basically took those moments to go the bathroom or get some food!!

      Reply
  2. SOURAV PAL

    How To comapre two Excel sheets using vb and difference of two excel sheets should generate a report with cell no and difference values of two cell.

    Reply
  3. fawzar

    Corrected code on downloaded Sample Excel file.
    Use this code – workign successful!
    Thank for Author.
    ****************
    Private Sub Workbook_Open()
    Dim nextblankrow As Long
    Dim lastrow As Long
    Sheets(“Report”).Select
    Sheets(“Report”).Cells.ClearContents
    Range(“A1”).Value = “Name”
    Range(“B1”).Value = “Mobile”
    Range(“C1”).Value = “Phone”
    Range(“D1”).Value = “Interested in”
    For i = 1 To Sheets.Count – 1
    lastrow = Sheets(i).Range(“A” & Rows.Count).End(xlUp).Row
    ‘Sheets(i).Range(“A2:D50”).Copy
    Sheets(i).Range(Cells(2, 1).Address, Cells(lastrow, 4).Address).Copy
    nextblankrow = Sheets(“Report”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Row
    Sheets(“Report”).Cells(nextblankrow, 1).PasteSpecial Paste:=xlPasteValues
    Next
    Cells(1, 5).Select
    Application.CutCopyMode = False
    Sheets(“Report”).Range(Cells(1, 1), Cells(nextblankrow, 4)).Columns.AutoFit

    End Sub
    *************

    Reply

Leave a Reply

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