Create Reports from Raw Data with Loops Auto Filter VBA

How to create reports automatically from raw raw data using a for loop or Auto-Filter with VBA. Creating reports from raw Excel data is one of the most important activities. There are a variety of ways to create reports like Advanced Filter, Pivot tables, etc. But using VBA we can automate the process quickly and easily.
Watch the training video below before you dive into the complete VBA code:

You can watch this video on YouTube.

Using a for loop to create reports:
Sub myLedger()

Dim lastRow As Long, i As Long, erow As Long

lastRow = Sheets(“Day book purchase”).Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

If Sheets(“Day book purchase”).Cells(i, 3) = “Bakery item” Then
Sheets(“Day book purchase”).Range(Cells(i, 1), Cells(i, 6)).Select
Selection.Copy

‘Sheets. Open (Bakery items”)
Sheets(“Bakeryitem”).Select
lastRow = Sheets(“Bakeryitem”).Range(“A” & Rows.Count).End(xlUp).Row

erow = lastRow + 1

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save

Application.CutCopyMode = False
End If
Sheets(“Day book purchase”).Select
Next i
End Sub

You can extend the usability of the looping process by using ‘IF ELSE‘ statements to take care of multiple reports or a CASE statement.
Also note that the category of item names matches the worksheet names so that the use of variables is minimized and processing is faster.

Using Auto-Filter with VBA to create reports:
Sub filterData()
Dim myvalue As String
myvalue = InputBox(“Enter the item you wish to extract”)
Sheets(myvalue).Select
Sheets(myvalue).Cells.Clear
Range(“A1”) = “Date”
Range(“B1”) = “Item”
Range(“C1”) = “Category”
Range(“D1”) = “Quantity”
Range(“E1”) = “Rate”
Range(“F1”) = “Total”
Range(“A1:F1”).Font.Bold = True
Range(“A1:F1”).Font.ColorIndex = 5
Sheets(“day book purchase”).Select
Dim lastRow As Long
lastRow = Sheets(“Day book purchase”).Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

Dim lastColumn As Long
lastColumn = Sheets(“Day book purchase”).Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column

Sheets(“Day book purchase”).Range(Cells(1, 1), Cells(lastRow, lastColumn)).AutoFilter Field:=3, Criteria1:=myvalue
Sheets(“Day book purchase”).Range(Cells(2, 1), Cells(lastRow, lastColumn)).Copy
Sheets(myvalue).Select

erow = Sheets(myvalue).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets(myvalue).Paste Destination:=Worksheets(myvalue).Rows(erow)
Sheets(“Day book purchase”).Select
Sheets(“Day book purchase”).Range(Cells(1, 1), Cells(lastRow, lastColumn)).AutoFilter Field:=3
ActiveWorkbook.Save

End Sub

8 thoughts on “Create Reports from Raw Data with Loops Auto Filter VBA

  1. Ali Niaz

    Let say i have this type of data in Sheet 1
    Date Inv.# Customer Name Invoice Amount Discount Net Amount
    01-09-16 3806 ABC 50,000.00 (500.00) 49,500.00
    08-09-16 3807 xyz 20,600.00 (1,600.00) 19,000.00
    15-09-16 3808 QDR 63,500.00 (3,200.00) 60,300.00
    22-09-16 3809 ABC 12,500.00 (500.00) 12,000.00
    29-09-16 3810 Ryz 54,100.00 (500.00) 53,600.00
    06-10-16 3811 KML 7,800.00 (500.00) 7,300.00
    09-10-16 3812 xyz 57,000.00 (500.00) 56,500.00

    in Sheet 2

    Date CM # Customer Name Payment Method Amount
    08-09-16 5080 ABC Cash 4500
    11-09-16 5081 xyz Cheque 6800
    14-09-16 5082 QDR Credit card 78000
    17-09-16 5083 ABC Cash 45000
    20-09-16 5084 Ryz Cheque 23500
    23-09-16 5085 KML master card 6100
    26-09-16 5086 Ryz Cheque 52500
    29-09-16 5087 KML Cash 8793
    02-10-16 5088 Ryz Cash 11400
    06-10-16 5089 xyz Cash 52000

    in Sheet 3 How automaticaaly i can generate this type of report automatically

    Date Customer name Invoice Name CM# Invoice Amount Payments Bal
    01-09-16 ABC 3806 49,500.00 49,500.00
    22-09-16 ABC 3809 12,000.00 61,500.00
    08-09-16 ABC 5080 4,500.00 57,000.00
    17-06-16 ABC 5053 45,000.00 12,000.00

    08-09-16 xyz 3807 19,000.00 19,000.00
    09-10-16 xyz 3812 56,500.00 75,500.00
    11-09-16 xyz 5081 6,800.00 68,700.00
    06-10-16 xyz 5089 52,000.00 16,700.00

    Reply
  2. Mitesh Patel

    HI SIR
    CAN I GET SAMPLE EXCEL FILE FOR “Create Reports from Raw Data with Loops Auto Filter VBA”
    ITS VERY HELPFUL FOR ME.

    THANK YOU VERY MUCH SUPPORTING.

    Reply
  3. Naresh Dixit

    Please provide the sample xl file for “creating report from raw data with auto filter with VBA”. It will be helpful in using the VBA codes provided in the video. Thanks a lot.

    Reply
  4. Mohamed Saleem azhar

    Dear Mr. Dinesh,

    Thank you very much for this information.

    When I customised the same code that you had given in to my worksheet, I am getting a error. I can send you the worksheet if you could share me your email.

    However the error is method ‘range of object’_global’ failed

    is _ making any issue in here

    Reply
  5. prem singh chauhan

    Please make this video on hindi.
    Kiya ise alag alag sheet se use kar sakte hain.
    Data 2 ya 3 sheet main ho or vaha se data le kar ek sheet main dikhaye.
    Example
    Maine date enter kiya
    20/07/2017
    To is date k jitne bhi work ho alag alag sheet main vo ek list k roop main ek sheet main dikhaye…
    Like a daybook

    Reply
  6. prem singh chauhan

    Please sir
    Help me
    Agar hindi main nahi bana payenge video to english main hi create kar dijiyega.
    Or ise ek button k saath banaye.

    Reply
  7. Antranik

    I am looking for a help to solve an issue in existing worksheet for oil and gas calculations using VBA
    I have a specific column (raw data) in sheet1 transferred to sheet2, however, I calculation sheet (sheet2) I am facing an issue when we usually place a comment I a row that will interrupt the series subtraction calculation since it considers the comment row as zero value. so my question is: how I can bypass the comment row and go directly to the last previous cell within the same column that has a value. I used many options such as cells.(xlup) or special cells options but did not work

    Reply

Leave a Reply

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