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
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
Data and results do not make sense. Please revise carefully and submit again.
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.
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.
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
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
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.
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
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
sir,
aapka mob. no. kya h. sir hm aapse baat krna chahte h.
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
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
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
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
DEAR SIR,
I HAVE COPIED AND PASTED THE CODE ON TO MY MODULE, IT TRANSFERS DATA BUT NOT SELECTIVE AS ENTERED INTO MY INPUT BOX, BUT ALL THE DATA.
WHAT HAVE I DONE INCORRECTLY.
THANK YOU