Debug Error bugging me!!

[TPD]

OK,..This one is driving me nuts!!!

I created this form in Excel that contains 7 different tabbed worksheets within it.
So far so good!

I have a button that when pressed, automatically prints out all 7 worksheets at once to the printer!

Here's the problem. I entered the code as such (see below) that it DOES work,..but if anyone changes the name of the workbook,..they get a debug error message!!! Is there something I can add in the lines of code so that they can name the workbook anything they want and it will still print. I tried *.xls in place of the Project Workbook.xls assuming "*" meant wildcard,..but it didn't work.

As other folks use this form,..they often change the name of the file or workbook to Project Workbook 2 or 3 or they add a date or something and then it throws it off.

Application.Run "'Project Workbook.xls'!Print_Overview"
Application.Run "'Project Workbook.xls'!Print_Contact_List"
Application.Run "'Project Workbook.xls'!Print_Issue_Tracker"
Application.Run "'Project Workbook.xls'!Print_Site_List"
Application.Run "'Project Workbook.xls'!Print_Project_Plan"
ActiveWindow.SmallScroll Down:=3
Application.Run "'Project Workbook.xls'!Print_Exec_Summary"
Application.Run "'Project Workbook.xls'!Print_Closeout"
ActiveWindow.SmallScroll Down:=3

Thanks,

Todd

[pikus]

Try this out and let me know if you have any problems. - pikus

For x = 1 To Worksheets.Count
Worksheets(x).PrintOut
Next x

[bhofsetz]

You should be able to use
ThisWorkbook (outside the quotes) which will find the worksheets in the workbook which contians the macro code for printing.
As long as people don't go changing the worksheet names this should work for you.
If you think they may start changing worksheet names you can call the worksheets by relative reference.
Sheets(1) , Sheets(2) , etc...

Give this a try

Application.Run "'" & ThisWorkbook & ".xls'!Print_Overview"

repeat for the rest of your worksheets.
untested but a starting place.

HTH

B

[swatsp0p]

If you want to print <ALL> sheets in the workbook:

Sub PrintAll()
ActiveWorkbook.PrintOut
End Sub

is the code to use.

It doesn't care the name of the sheets or the workbook!

HTH

[TPD]

If you want to print <ALL> sheets in the workbook:

Sub PrintAll()
ActiveWorkbook.PrintOut
End Sub

is the code to use.

It doesn't care the name of the sheets or the workbook!

HTH

Uh,...this did the trick!!! Thanks guys!! I'm off and running!!!

td

[bhofsetz]

First of all do you want to print out all the worksheets or just the ones named?
Secondly the code you posted won't work as is because you have the lines with the print statements commented out (with the single quote ' at the beginning).

If you want all worksheets of the active workbook printed then use Bruce's suggestion.
If you want just the first several worksheets printed you can modify Pikus code and loop from 1 to (highest worksheet you want printed) OR loop from (first worksheet you want printed) to (highest worksheet you want printed)

Keep you hair and go pull out her's

[swatsp0p]

Todd: Glad it worked for you. Hunt down Catherine and rub her nose in it then call Hairclub for Men!

Cheers!

Bruce