create a report summary page by group

[PippaThePointer]

Looking for insperation on what direction to take.
Im looking into a problem where a person recieves xls data that looks like a static pivot table. Each row is a location with about 4 columns of location details and then a number of columns that will vary each time this file comes in that conains a product id as the header and a quanty agaist each store.
They wanta pdf or report for each location (row) and shows the location details at the top an then lists the product codes and qty below.

I have some good macro that can loop through the rows and create pdf of each location but thats based on a fixed number or column name for each product. This list of products will change all the time. Also because the product id are long and similar the end users wants an index count for the products. So they get labelled 'product 1, 2, 3' etc.
So im thinking do i used a bit of power query to pull in the data each time, sort, unpivot and add a index. Then run some macro to push out the pdf but im not sure how to make the unpivoted list show as a report for each category.
What path to take to get the unpivitod list to run some sort of report per category. Should i make a new dynamic pivot table and try and use slicer (havnt used them before). Is there a better cleaener way to do it all in PQ or all in macro?

 

[DanteAmor]

Hello @PippaThePointer:

You will undoubtedly receive help, whether for PQ or Macro, but, at least for me, it is important that you add examples 3 or 4 examples of how the data will be, and very important, that you add the result of each one that you want from each example.

Check the following link, you will see that the OP gave his examples and the result and the help were resolved the first time:

populate summary sheet by merge amounts based on two columns(ID,CUSTOMER)

Hi I need to create summary for report in SUMMARY sheet ABCDEFGH12FROM DATETO DATE3456ITEMCUSTOMERIDOPENINGBUSEBU TOTALSE TOTAL78910111213141516171819202122232425262728 the data in sheets ABCDEF1ITEMCUSTOMERIDQTY...

www.mrexcel.com

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

 

[PippaThePointer]

I should have added this at the start. Thank you @DanteAmor.
Below is a small sample of my data and a rough example of the pdf report i would eventually print out along with the xcel sheet.
The number of stores (rows) and the number of Products (row J onwards) will vary.
I need to add a index number to the Products (starting from 1 through to end)
Not sure if i should create a macro that makes a worksheet for every Row and edits the worksheet to display as desired and then save each sheet as a pdf.
or
to make it a single worksheet report that then has a filter and the macro turns off and on the filter and saves as pdf through a macro loop.
or
maybe there is another simple way I havnt thought of.

I have got some Macro skills learnt from this site and some PQ skills. I hanvt used Pivot tables much. Im unsure of the best practice to tackle this.

Small Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Store #	Store Name	Store Business Manager	Address1	Address2	Suburb	State	Postcode	Phone	Product A	Product B
2	201	A								100	200
3	204	B								50	150
Sheet1

 

[DanteAmor]

The number of stores (rows) and the number of Products (row J onwards) will vary.

Products always start in column J onwards? <--- Answer this question, it is very important for macro.

Not sure if i should create a macro that makes a worksheet for every Row and edits the worksheet to display as desired and then save each sheet as a pdf.
or
to make it a single worksheet report that then has a filter and the macro turns off and on the filter and saves as pdf through a macro loop.
or
maybe there is another simple way I havnt thought of.

I recommend the following:

- Create the PDF format you want in a sheet, we will call this sheet "PdfFormat". Note: this sheet must be empty, that is, it must only have the formats in the cells. For example:​

Once the "PdfFormat" sheet is created, the macro will do the following:

- For each store, the "PdfFormat" sheet will be copied to a new workbook.​

- The macro will fill the new workbook with the data of the store and the products.​

- It will save the new pdf workbook with the name of the store in the same folder where you have the file with the macro.​

So if you follow the previous recommendations, run the following macro:

Sub ReportSummary()
  Dim shF As Worksheet, sh1 As Worksheet, sh2 As Worksheet
  Dim wb As Workbook
  Dim i As Long, j As Long, n As Long, nRow As Long, initialRow As Long
  Dim tot As Double
  Dim store As String
  
  Application.ScreenUpdating = False
  Set sh1 = ThisWorkbook.Sheets("Sheet1")         'Data sheet name
  Set shF = ThisWorkbook.Sheets("PdfFormat")      'Sheet name with pdf format
  initialRow = 3                                  'Initial row where the stores will begin to be placed
  
  'Loop every store
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    store = sh1.Range("B" & i).Value
    tot = 0
    n = 0
    nRow = initialRow
    
    'Copy format sheet
    shF.Copy
    Set sh2 = ActiveWorkbook.Sheets(1)
    sh2.Range("A1").Value = store                 'Cell A1 with the name of the store
    
    'Loop every product
    For j = sh1.Columns("J").Column To sh1.Cells(Columns.Count).End(1).Column
      n = n + 1
      tot = tot + sh1.Cells(i, j).Value
      shF.Rows(initialRow).Copy sh2.Rows(nRow)
      sh2.Range("A" & nRow).Value = n
      sh2.Range("B" & nRow).Value = sh1.Cells(1, j).Value
      sh2.Range("D" & nRow).Value = sh1.Cells(i, j).Value
      nRow = nRow + 1
    Next
    
    'Put the total 2 rows down
    shF.Rows(initialRow).Copy sh2.Rows(nRow & ":" & nRow + 2)
    sh2.Range("A" & nRow + 2).Value = "Total"
    sh2.Range("D" & nRow + 2).Value = tot
    
    'Save the new pdf
    sh2.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=ThisWorkbook.Path & "\" & store & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    sh2.Parent.Close False
  Next
  
  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[PippaThePointer]

Products always start in column J onwards? <--- Answer this question, it is very important for macro.



I recommend the following:

- Create the PDF format you want in a sheet, we will call this sheet "PdfFormat". Note: this sheet must be empty, that is, it must only have the formats in the cells. For example:​

View attachment 116774​

Once the "PdfFormat" sheet is created, the macro will do the following:

- For each store, the "PdfFormat" sheet will be copied to a new workbook.​

- The macro will fill the new workbook with the data of the store and the products.​

- It will save the new pdf workbook with the name of the store in the same folder where you have the file with the macro.​

So if you follow the previous recommendations, run the following macro:

Sub ReportSummary()
  Dim shF As Worksheet, sh1 As Worksheet, sh2 As Worksheet
  Dim wb As Workbook
  Dim i As Long, j As Long, n As Long, nRow As Long, initialRow As Long
  Dim tot As Double
  Dim store As String
 
  Application.ScreenUpdating = False
  Set sh1 = ThisWorkbook.Sheets("Sheet1")         'Data sheet name
  Set shF = ThisWorkbook.Sheets("PdfFormat")      'Sheet name with pdf format
  initialRow = 3                                  'Initial row where the stores will begin to be placed
 
  'Loop every store
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    store = sh1.Range("B" & i).Value
    tot = 0
    n = 0
    nRow = initialRow
   
    'Copy format sheet
    shF.Copy
    Set sh2 = ActiveWorkbook.Sheets(1)
    sh2.Range("A1").Value = store                 'Cell A1 with the name of the store
   
    'Loop every product
    For j = sh1.Columns("J").Column To sh1.Cells(Columns.Count).End(1).Column
      n = n + 1
      tot = tot + sh1.Cells(i, j).Value
      shF.Rows(initialRow).Copy sh2.Rows(nRow)
      sh2.Range("A" & nRow).Value = n
      sh2.Range("B" & nRow).Value = sh1.Cells(1, j).Value
      sh2.Range("D" & nRow).Value = sh1.Cells(i, j).Value
      nRow = nRow + 1
    Next
   
    'Put the total 2 rows down
    shF.Rows(initialRow).Copy sh2.Rows(nRow & ":" & nRow + 2)
    sh2.Range("A" & nRow + 2).Value = "Total"
    sh2.Range("D" & nRow + 2).Value = tot
   
    'Save the new pdf
    sh2.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=ThisWorkbook.Path & "\" & store & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    sh2.Parent.Close False
  Next
 
  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

This works well thanks.