What I need;
I have a sales master data (with over 20 columns).
It contains the following:
S/N, Customer, Invoice date, Due date, vessel, producer, fiscal regime, quantity, price, sales value, date paid, amount paid, variance, remark, etc
1.) I need a macro to group this report by invoice type, sum each group, with the column header and group title for each group on same sheet.
2.) Copy 1 or more groups to form a another report on separate sheets
3.) Write the summary (like a pivot table) for some of the column headers. Eg, Fiscal regime, sum by each producer with columns quantity, sales value and receipts
4.) Group 1 or more of the summaries to form another report on separate sheets.
Not to write too much, I am able to provide more clarity when we start.
Thanks for your kind assistance. I have attached a sample of what I have done so far
Thank you.
I have a sales master data (with over 20 columns).
It contains the following:
S/N, Customer, Invoice date, Due date, vessel, producer, fiscal regime, quantity, price, sales value, date paid, amount paid, variance, remark, etc
1.) I need a macro to group this report by invoice type, sum each group, with the column header and group title for each group on same sheet.
2.) Copy 1 or more groups to form a another report on separate sheets
3.) Write the summary (like a pivot table) for some of the column headers. Eg, Fiscal regime, sum by each producer with columns quantity, sales value and receipts
4.) Group 1 or more of the summaries to form another report on separate sheets.
Not to write too much, I am able to provide more clarity when we start.
Thanks for your kind assistance. I have attached a sample of what I have done so far
VBA Code:
Option Explicit
Sub Filter_Restaurants()
Dim c As Range, i, z, lr As Long
Dim lastrow As Long, nextrow As Long
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
'Application.ScreenUpdating = False
' test for entries in the input range - exit sub if no entries
If WorksheetFunction.CountA(Sheet1.Range("A1:A10")) < 1 Then Exit Sub
'prepare output sheet to receive new filter results
Sheet2.Cells.Clear
With Sheet1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
' count number of entries in the input cells
i = WorksheetFunction.CountA(.Range("A1:A9"))
'turn off any previous filters
If .AutoFilterMode = True Then .AutoFilterMode = False
'loop through input cells and filter and copy results
For Each c In .Range("A1:A" & i)
nextrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 3
.Range("A10:A" & lastrow).AutoFilter field:=1, Criteria1:="=" & c.Value
.Range("A10:P" & lastrow).SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A" & nextrow)
With Worksheets("Sheet2")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
'.Cells(lr + 1, 1).Value = "Total"
'lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lr + 1, 1).Value = "Total"
.Cells(lr + 1, 10).Resize(, 8).FormulaR1C1 = "=SUM(R3C:R[-1]C)"
With .Range(.Cells(lr + 1, 1), .Cells(lr + 1, 16))
.HorizontalAlignment = xlRight
.Font.Bold = True
.Interior.Color = rgbLightBlue
End With
'.Range("A & lastrow").CurrentRegion.Weight = xlThin
End With
Next c
'turn off filtering
.AutoFilterMode = False
End With
Sheet2.Activate
Sheet2.Range("A4").Select
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True
'Application.ScreenUpdating = True
End SubThank you.
