Thread: Printing different worksheets from multiple workbooks

I have around 8 workbooks each with about 30 worksheets in them.

Is there anyway i can create a new workbook with a sheet containing several buttons. When one of the buttons is pressed it will print out a number of specific sheets from the workbooks?

Good afternoon andycreighton

Welcome to the forum!

This sounds like just the kind of job that the macro recorder was built for. Go to Tools > Macro > Record new macro and go through the tasks you want to do - opening, selecting sheets, printing closing etc - then click on the stop recording button.

Design a button using the Forms toolbar, right click the button and assign the macro you've just created to it.

Good luck!

HTH

DominicB

Is there any way you can print a sheet without haveing to unhide it??

Cheers

Andrew C

I attempted recording a macro in a new work book to print them all but got Run-Time error 9; Subscript out of range.

Also when i looked at the VBA the code read....

ActiveWindow.ScrollWorkbookTabs Sheets:=7
Sheets("GRAYSTONE").Select

Im assuming that means the workbook has to physically be open to work. Is there anyway to do it without having to open them beforehand?

Thanks

Andy

Hi andycreighton

No, you have to open the workbook to print it. However, you can hide the opening of a spreadhseet from the user by using the Application.ScreenUpdating command. The macro below has been part recorded by my, and I then added manually the first and last line which will stop the user from seeing the file open and close. All they will experience is a slight delay.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/01/2007 by Dominic Brown1
'

'
Application.ScreenUpdating = False
    Workbooks.Open Filename:="D:\Work\Analysis-Output.xls"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWindow.Close
Application.ScreenUpdating = True
End Sub

HTH

DominicB