November 6, 2016

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

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

erow = lastRow + 1

ActiveSheet.Cells(erow, 1).Select

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”)
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, _

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

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

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

End Sub

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

  1. 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

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


  3. 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.

  4. 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

  5. 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.
    Maine date enter kiya
    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

  6. 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.

  7. 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

    1. Dear Mr. Dinesh,

      Thank you very much for this information.
      I get an error message in the Auto Filter method of the range class.
      Error ‘1004’ in execution time

  8. Hi Dinesh
    This Video Is Great Is It Possible For You To Add A List Box Or Combo Box To The Search Box To Include All Of The Categories So I Only Have To Select An Item From The List Or Combo Box This Will Also Allow Me To See All Items Listed ThankYou So Much
    Kind Regards Steven Jones

  9. Can Anyone Please Help Hi EveryOne This Code Is Perfect For What I need My Problem Is That The First Code Works Fine I Just Had To Change The Number Of Columns My DataBase Is A Bit Smaller But When Ever I Try And Run The User Defined Filter Version It Copies The Correct Amount Of Rows But It Duplicates The Data To the End Of The Spread Sheet I Have About Fifty Copies Of The Data Can Someone Please Help Thanks Eveyone

  10. Hi Every One Can Some Please Let Me Know How To Modify The Filter Data Code To Work With Only 4 Columns In Stead Of 6 Columns As It Is Written Thanks

  11. I have a Pivot table that I have filtered to create multiple Range as per the filters name at the top . I need to send these Pivot range as per the name selected and everytime it need to beslected new until the last one selected and send to multiple emails as an attachment. Each email should only receive their sheet and not get another sheet or the workbook.

    Appreciate your assistance with this

    1. DEAR SIR,


  12. Hello,

    Thank you for this code.

    However, when it comes to extracting the final row of data in a filter according to let’s say “fruits.” It copies all the data in the table and not the rows corresponding to “fruits” for example.

    This only happens when “fruits” is the last row.

    Any help would be great. Cheers.

Comments are closed.