Printing hiddem sheets

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
I have recorded a macro to print select sheets in a workbook and have hidden the sheets but when i want to print the specified sheets i get this error:

Run-time error '1004'

select method of sheets class failed

Any ideas on how to resolve this error besides having the sheets be visible ?

This is the code that i am using:

Sub SM4PGPMPPRINTING()
'
' SM4PGPMPPRINTING Macro
' Macro recorded 9/24/2002 by Administrator
'

'
Application.ScreenUpdating = False
Sheets(Array("Cover pg 1", "Mg Goal Pg & Mid-Final", _
"Guiding Values Goal & Mid-Final", "Mg Career dev & Final Rating")).Select
Sheets("Cover pg 1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Cover pg 1").Select
Range("I42:K42").Select
Application.ScreenUpdating = True
End Sub


Thanks...
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Roy,

Use Worksheets("YourSheetName").Visible = True at the start of your code, but after the Screenupdating=False statement. Then at the end, but before the Screenupdating=True statement, reset to False to hide them again.

HTH
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
You may still have screen flicker going on, regardless of Application.ScreenUpdating = False. Plan your code carefully.

Printing from a hidden sheet would be nice,but until then, just make sure you have all the cells set up just the way they should be, and make the sheet visible immediately before .Printout.

I have the same issue; decided the easy way to deal with it was to make sure the userform covered the entire screen. Speeded up the code, it seems.


Now considering if the hidden sheets need to be a seperate sheet. The main sheet has the scrollarea restricted as it is, so it might be possible to put the 'scratchpad beyond the scroll boundary.

This is a common challenge, it seems.
 

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
Richie..either i copy something wrong or i missed a step somewhere. Did all your steps and i got an error " Compile error wrong number of arguments"

this is the code i am using:

Sub SM4PGPMPPRINTING()
'
' SM4PGPMPPRINTING Macro
' Macro recorded 9/24/2002 by Administrator
'

'
Application.ScreenUpdating = False
Worksheets("Cover pg 1", "Mg Goal Pg & Mid-Final", _
"Guiding Values Goal & Mid-Final", "Mg Career dev & Final Rating").Visible = True
Sheets(Array("Cover pg 1", "Mg Goal Pg & Mid-Final", _
"Guiding Values Goal & Mid-Final", "Mg Career dev & Final Rating")).Select
Sheets("Cover pg 1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Cover pg 1").Select
Range("I42:K42").Select
Worksheets("Cover pg 1", "Mg Goal Pg & Mid-Final", _
"Guiding Values Goal & Mid-Final", "Mg Career dev & Final Rating").Visible = False
Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,144,328
Messages
5,723,730
Members
422,512
Latest member
MHau5

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