Macro to copy/paste to other worksheet until end

[redcorvette]

Hi all. Longtime lurker first time posting. I've tried searching the forum and others but don't see one that fits my exact needs. Would really appreciate some help, thank you!

I have a pivot in worksheet "List" that has the first unique data value in Cell B2 and subsequent values are also located in column B. My macro starts by refreshing this pivot as the unique number of rows will change weekly. I need to copy the first result in B2 and paste into worksheet "REPORT" cell $F$17. My macro then prints this sheet. I then need it to loop back to worksheet "List" copy cell B3 from the pivot and do the same paste/print as above and repeat until there are no further values in column B. I have the Grand Total removed from my Pivot and it would be a black cell as the last one.

Here is what I have. My assumption is the part highlighted in Red needs something to tell it to loop to the next cell down in the pivot but I cannot figure out what to put. Any tips are greatly appreciated! I'm sure I've butchered a lot here.

Sub Macro5()
'
' Macro5 Macro
'

'
Sheets("List").Select
Range("B2").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
Sheets("List").Select
Range("B2").Select
Selection.Copy
Sheets("REPORT").Select
Range("F17").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("List").Select
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop


End Sub

 

[Zot]

Without sheet that shows the pivot table, I think it is hard to define range of your pivot table. If you are working just like normal sheet, here is what I will do:

Not sure if this will work. No actual testing.

Sub Macro5()

Dim cell As Range, rngData As Range
Dim wsList As Worksheet, wsReport As Worksheet

Set wsList = ActiveWorkbook.Sheets("List")
Set wsReport = ActiveWorkbook.Sheets("REPORT")

Set rngData = wsList.Range("B2", wsList.Cells(Rows.Count, "B").End(xlUp))

For Each cell In rngData
    cell.Copy wsReport.Range("F17")
    wsReport.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next

End Sub

For your reference, the article shows how you address the range in pivot table.

Referencing Pivot Table Ranges in VBA - Peltier Tech

This tutorial explains how to reference various parts of a pivot table using VBA code.

peltiertech.com

 

[redcorvette]

This works! Wow, much cleaner than my rudimentary one. My Pivot table is only one column "B" and starts with Data on B2. Pivot table name is just PivotTable2. And it is the only thing on the worksheet "List".

Would this macro refresh PivotTable2 at the beginning, prior to starting? The reason is because the number of rows in the pivot could change each time.

 

[Zot]

This works! Wow, much cleaner than my rudimentary one. My Pivot table is only one column "B" and starts with Data on B2. Pivot table name is just PivotTable2. And it is the only thing on the worksheet "List".

Would this macro refresh PivotTable2 at the beginning, prior to starting? The reason is because the number of rows in the pivot could change each time.

I just added the refresh. Hope this works

Sub Macro5()

Dim cell As Range, rngData As Range
Dim wsList As Worksheet, wsReport As Worksheet

Set wsList = ActiveWorkbook.Sheets("List")
Set wsReport = ActiveWorkbook.Sheets("REPORT")

wsList.PivotTables("PivotTable2").PivotCache.Refresh

Set rngData = wsList.Range("B2", wsList.Cells(Rows.Count, "B").End(xlUp))

For Each cell In rngData
    cell.Copy wsReport.Range("F17")
    wsReport.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next

End Sub