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

2 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

Leave a Reply

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