Total Number of Pages per Worksheet

[mcquilr]

Hi

I am trying to print an entire workbook to pdf at the same time. I have numberous worksheets in the workbook and each needs to be a seperate document which in the footer states page x of y.
I have managed to get all worksheets to print out starting at page 1 and running through my problem is the total number of sheets is coming out as the total number of pages per workbook instead of per worksheet. Can I use VBA code to get around this?

Thanks

 

[Andrew Poulsom]

Welcome to the Board.

To do that you will need to print one worksheet at a time rather than group the sheets and print all at once.

 

[mcquilr]

Thanks for the Weclome Andrew.

Is there no way of doing this in code? I know I can do it worksheet by worksheet but this is very tedious for something that will be used quite a lot.

The other alternative I was considering was using this code to automatically print each worksheet to pdf

Sub Print_particular_selected_sheets()
Dim Sheet_n As Worksheet
For Each Sheet_n In ActiveWindow.SelectedSheets
Sheet_n.PrintOut
Next
End Sub

My problem with this code is that it still requires the operator to type in a name against each tab and I cannot seem to get it to work where it automatically prints each one to pdf, saving as the tab name.

Any ideas?

Thanks
Rachel

 

[Andrew Poulsom]

If you record a macro while printing one sheet to pdf what does it look like?

 

[mcquilr]

It works fine if I do that but the macro only records the print function, it doesn't record the saving of the pdf file

 

[Andrew Poulsom]

Please post the code you get.

 

[mcquilr]

Sub XX()
'
' XX Macro

Sheets("V2532-00").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[Andrew Poulsom]

What PDF software are you using? That code suggests that you have already chosen a printer.

 

[mcquilr]

I am using acrobat standard 7.0

I'll try choosing the printer again

 

[mcquilr]

I then get this code:

Sub Macro5()
'
Sheets("V2532-00").Select
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

but is there a way that I can get it to save automatically with the name of the tab?