Author Archives: Dinesh Kumar Takyar

About Dinesh Kumar Takyar

Welcome to! 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: For a structured Excel VBA training course online you can visit:

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