pagesetup object

[Steven Cheng]

I believe that I should be able to "pass" the parameter settings of one
pagesetup on a worksheet to another and thus having the same headers,
footers, margins, print areas and such without having to declare each
setting....

private sub setupworksheetprintarea()

dim w as worksheet
dim wkb as workbook
dim pg as pagesetup

set w = thisworkbook.activesheet

set wkb = workbooks.add


set wkb.activesheet.pagesetup = w.pagesetup


end sub


I am only speculating but I think it should work...

 

[Dave Peterson]

It sure would be nice if it did. But I betting it didn't work when you tried
it, huh?

 

[Steven Cheng]

nope...not a chance...ugh....

It sure would be nice if it did. But I betting it didn't work when you tried
it, huh?

 

[NickHK]

Steven,
As Dave says, it won't work.
See the help for the PageSetUp property:
<Help>
Returns a PageSetup object that contains all the page setup settings for the
specified object. Read-only.
</Help>
Note the Read-Only. so:

Private Sub CommandButton2_Click()
Dim PSetUp As PageSetup
'This works
Set PSetUp = ThisWorkbook.Worksheets(1).PageSetup
'This fails
Set ThisWorkbook.Worksheets(2).PageSetup = PSetUp

End Sub

NickHK

 

[Dave Peterson]

There are a lot of settings that get transfered when you select multiple sheets
(have to be in the same workbook, though), then go into file|page setup and
click ok.

And the page setup stuff can really slow down your macro. Keep just the
settings you want to change.

Tom Ogilvy combined some tips from John Green and keepitcool in this post:

http://groups.google.co.uk/group/mi...*excel*+author:ogilvy&rnum=1#b3989b26c2cb850e

or
http://snipurl.com/1cfe3

 

[Dave Peterson]

The code that Tom included from John Green uses xl4 macros and can be much
faster.

 

[Steven Cheng]

thanks Dave. this little tidbit is pretty good.

The code that Tom included from John Green uses xl4 macros and can be much
faster.