Excel Page Setup & Print Settings Not Working In VBA ?? :: MrExcel Message Board
With Macromedia Shockwave Player, you can enjoy multimedia games, learning applications, and product demonstrations on the Web, using exciting new 3D technology. Get it at: http://www.macromedia.com/shockwave/download



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Page Setup & Print Settings Not Working In VBA ??
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

jdowski
Board Master


Joined: 22 Apr 2002
Posts: 178
Location: Oxford, Connecticut USA.

Status: Offline

 Reply with quote  

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.

Post Tue Dec 10, 2002 3:15 pm 
 View user's profile Send private message Send e-mail

Corticus
TheOfficeExperts


Joined: 01 May 2002
Posts: 1273
Location: Miami, FL
Flag: Usa

Status: Offline

 Reply with quote  

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:

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

to print the pages.

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


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

End Sub


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 ]

Post Tue Dec 10, 2002 4:04 pm 
 View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger

jdowski
Board Master


Joined: 22 Apr 2002
Posts: 178
Location: Oxford, Connecticut USA.

Status: Offline

 Reply with quote  

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.

Post Tue Dec 10, 2002 4:10 pm 
 View user's profile Send private message Send e-mail

Corticus
TheOfficeExperts


Joined: 01 May 2002
Posts: 1273
Location: Miami, FL
Flag: Usa

Status: Offline

 Reply with quote  

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

Post Tue Dec 10, 2002 4:15 pm 
 View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.