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 ‘house-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
For i = 1 To Sheets.Count – 1
nextblankrow = Sheets(“Report”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets(“Report”).Cells(nextblankrow, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets(“Report”).Range(Cells(1, 1), Cells(nextblankrow, 4)).Columns.AutoFit
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:
Download sample file by clicking on the Excel icon: