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