Automating New Report Creation from Updated Raw Data

How can we automate new report creation from updated raw data quickly and easily. Many of us enter raw data into a data sheet in Excel like data of customers or items and the related details. Often our manager would like to have a report onĀ  specific customers or items. The standard procedure is to perform an Auto-Filter, copy the data and paste it into a relevant worksheet. This process can be time consuming and error prone. We can automate the complete process using interesting and easy VBA code. watch the training video:


View this video on YouTube.

The item names and the worksheet names are the same. Here’s the complete VBA code to automate new report creation from updated raw data quickly and easily.

Sub createNewReport()

‘declare variables

Dim lastRow As Long, lastColumn As Long, erow As Long
Dim p As Long, q As Long, i As Long
Dim itemName As String
Dim worksheet_to_be_Cleared As String
lastRow = Sheets(“DataSheet”).Cells(Rows.Count, 1).End(xlUp).Row
lastColumn = Sheets(“DataSheet”).Cells(1, Columns.Count).End(xlToLeft).Column

‘Ask the user for the worksheet name where the new report will be created

‘Clear all the data in the worksheet except the headers

worksheet_to_be_Cleared = InputBox(“Enter the name of the worksheet to be cleared for new report.”, “New Report”)
p = Worksheets.Count
For q = 1 To p
If Worksheets(q).Name = worksheet_to_be_Cleared Then
End If
Next q

‘Next ask the user for the item name for which the report has to be created

itemName = InputBox(“Enter an item name exactly as in your data”, “Enter Item Name”)
For i = 2 To lastRow
If Cells(i, 1) = itemName Then
Range(Cells(i, 1), Cells(i, lastColumn)).Copy

p = Worksheets.Count

For q = 1 To p

If ActiveWorkbook.Worksheets(q).Name = itemName Then

erow = Worksheets(q).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Worksheets(q).Cells(erow, 1).Activate
Worksheets(q).Cells(erow, 1).PasteSpecial
End If
Next q
End If
Next i

‘Remove the selection or ant-like structure around the copied data

Application.CutCopyMode = False
End Sub


For clearing the data you can also use the macro VBA code below and it will work faster:

For q = 1 to Application.Worksheets.Count
If Worksheet_to_be_cleared = Worksheets(q).Name then
Exit For
End if
Next q

The above code exits the for loop as soon it finds the correct worksheet and clears it. Also you need not use the variable p by directly counting the worksheets using the worksheet count function.

Further Reading:

Nested Loops