Macro to copy/paste to other worksheet until end

redcorvette

New Member
Joined
Sep 1, 2021
Messages
2
Office Version
  1. 365
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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.
 

redcorvette

New Member
Joined
Sep 1, 2021
Messages
2
Office Version
  1. 365
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
 

Forum statistics

Threads
1,148,257
Messages
5,745,709
Members
423,968
Latest member
CHHeights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top