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

 

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
Worksheets(q).Activate
Range(“A2:E10000”).ClearContents
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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! 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: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 thoughts on “Automating New Report Creation from Updated Raw Data”

  1. Thanks for the tutorial Mr. Dinesh

    Having no idea about VBA

    Getting stuck on this line which shows in red not moving forward.

    worksheet_to_be_Cleared = InputBox(“Enter the name of the worksheet to be cleared for new report.”,“New Report”)

    then this line shows in red

    Range(“A2:K10000”).ClearContents

    then this line shows in red
    itemName = InputBox(“Enter an item name exactly as in your data”, “Enter Item Name”)

    tried to run and the error as

    Compile Error:

    Syntax Error

    Can you send your sample working file

    Tried to search your videos but got even more confused
    Thanks

  2. Hi Sir,

    Thanks for your patience in creating this valuable stuff.. My query here is “How to run a macro exactly without getting blanks when we are accessing the invoice on-hold report”.

    Scenario : i work for **** company, where i need to prepare on-hold invoice entry report from the raw data from ERP. we segregate the work to various teams based on different hold names like “qty rec hold, maxshipment hold, price variance hold and matching hold etc””.. In this scenario, there is no guarantee that the particular hold name we have in todays report will come in tomorrows report. Because some invoices can be processed without having any holds (good compliance invoice).
    My doubt here is, i have took a sample raw data with all type of hold names (A – Z) and run a macro and doing all sorting like remove duplicates, condtional foramtting etc and stop the macro. In this case when ever i run this macro in my future sheets the results should come same but not happening. Because same hold names will not be there in every day report.

    Please advise how to fix this.

Leave a Reply

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