Automate Printing from a Pivot Table?

[PolarBear]

Hello,

I have a pivot table which shows sales data for a group of teams. I wanted a macro to print out a page for each team (the team name is up on the page level). So, using the macro record function, I stepped through the teams, and it generated the following (only showing 2 examples):

Sheets("DAILY TE- CUSTOMER").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Team").CurrentPage =
"John Jones"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Team").CurrentPage = _
"Mary Smith"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

When I tried to run it, I got an error message "Syntax error" for line 2 (ActiveSheet.PivotTables... etc.)

Any thoughts as to what might be wrong?

Thanks,

Kevin

 

[BrianB]

I left this for a bit in case someone else had a similar problem.

I can see nothing wrong with your code, so we can only suspect that the workbook is corrupted in some way.

I would first try cleaning the code by copy/pasting into Notepad/remove the module/open a new module & copy/paste back.

If it still persists copy data to a new workbook and remake the pivot table etc.

 

[pcc]

try

Sheets("DAILY TE- CUSTOMER").Select 
ActiveSheet.PivotTables(1).PivotFields("Team").CurrentPage = 
"John Jones" 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
ActiveSheet.PivotTables(1).PivotFields("Team").CurrentPage = _ 
"Mary Smith" 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

 

[PolarBear]

Situation Resolved - sort of

BrianB seems to have hit it on the head; when I deleted the first print entry (John Jones), the rest of the macro worked fine, and all the other sheets printed with no problem. I tried re-entering it via cut and paste, and by typing it manually, but it keeps rejecting that entry.

I wonder if the way the field name shows up on the screen is in fact the way it is recorded in the Pivot table? Maybe it's not John_Jones, but John_ _Jones, where the "-" are spaces, or some other not easily visible difference between what I typed and what's really there. I know with Word there's a function to turn on/off non-printing characters; anything similar in Excel?