Page Setup & Print Settings Not Working In VBA ??

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
Hi Excel Experts,
I have recorded a macro where I select 3 worksheets in a workbook by clicking on the first one and shift-clicking on the last one to select all three sheets. I then go into the page setup and set my various settings, landscape, fit to 1 page tall, fit to 1 page wide, footers, etc. However, when I run the macro, none of the settings take even though I am getting no error messages. I made sure my zoom setting was set to false so that the fit to 1 page tall/wide would take but still no positive effect. Will I have to select each sheet individually and configure the page/print settings or am I missing something else ??

Thanks,

Joe Dowski
Woodbury, CT. USA.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Well I'm not an Excel expert but...

I think this will help. Select each of the sheets you need to print. Select View|Page Break Preview. In page break preview set your page breaks like you want the page to look, then use code like:<pre>Sub printsheet()
Sheets("Sheet1").PrintOut Copies:=1, Collate:=True
Sheets("Sheet2").PrintOut Copies:=1, Collate:=True
Sheets("Sheet3").PrintOut Copies:=1, Collate:=True
End Sub</pre>
to print the pages.

You can 'force' page breaks like:<pre>Sub printarea()

With Sheets("Sheet1")
.PageSetup.printarea = "$A$1:$G$20"
.PrintOut Copies:=1, Collate:=True
End With

End Sub</pre>

edit:
I wanted to add that 'page setup' is 'sheet sensitive', in that adjustments you make to page breaks, orientation, margins are saved with the page from which you selected 'page setup' If you save every page's page setup like you want, when you reopen the workbook, those settings will remain.

HTH,
Corticus
This message was edited by Corticus on 2002-12-10 11:10
 
Upvote 0
Corticus,

I understand your reply, and I have specified the print area in code as well. What I am acutally have the most trouble with is getting the "fit to 1 page tall" and "fit to 1 page wide settings". That fact that my headers and footers aren't taking either just seems to be another symptom of the same problem.
Any thoughts??

J.
 
Upvote 0
What are the dimensions of the pages?

Perhaps it is too big? I have never had your problem occur when I forced the print macro to set the print area. Really, if you can get 'print preview' to display the sheet right, and you use the first code I provided, it should print what you see in 'print preview'.

You could send me a dataless copy and I'll look at it if you want:
corticus@hotmail.com

Its hard to recreate your dilemma without knowing what the sheet looks like, but I'll be around if you repost.

HTH,
Corticus
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top