Category Archives: Excel VBA 2013

Learn Excel VBA in version MS Excel 2013

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
Worksheets(q).Activate
Range(“A2:E100000”).Clear
End If
Next q

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

Sheets(“DataSheet”).Activate
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
Worksheets(q).Activate

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
Sheets(“DataSheet”).Activate
Next i

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

Application.CutCopyMode = False
End Sub

Further Reading:

Nested Loops