CODEWITHSUNDEEP
excelexcel-2010vba
user1759942
user1759942

Reputation: 1350

Is there an easy way to copy the ActiveSheets .PageSetup?

Seems simple and probably a case of "It's monday" but I can't find the answer to this.. without having to record all the pagesetup variables in strings beforehand, is there a way to copy a worksheets .pageSetup property?

in a macro im running, I create a new page, paste some data, change a bunch of pagesetup settings, then print. I was hoping there was a way i could save the page setup before hand, and apply it again afterwards so my users don't have to worry about fixing settings ever.

I tried:

dim ws as worksheet
set ws = ActiveSheet.pageSetup

'settings change / print

ActiveSheet.pagesetup = ws.pagesetup

that doesnt work because ws.pagesetup is now linked to activesheet.pagesetup, so as soon as i change the active sheets settings, ws's settings get changed too.

I also tried set ws = sheets(1) because the new page is never the first page, but then with that one ActiveSheet.pagesetup = ws.pagesetup doesnt work either, it says object doesn't support this property or method

Is there a simple way? must I have 20 different string variables to hold all the current pagesetup variables?

Thanks

Upvotes: 0

Views: 467

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Rather than coping page setting from a master sheet to other sheets:

  1. Create the master sheet
  2. Establish the page settings for this single sheet
  3. Make copies of the master
  4. Populate the copies with data, formulas, etc.

In other words, anticipate your needs and setup the sheets in advance.

Upvotes: 1

Bigtree
Bigtree

Reputation: 199

When I want to accomplish something like this I'll save all my formatting and pagesetup stuff to a hidden sheet. At the right time I'll have my code copy and paste everything where I need it. In this case it would probably be easiest to start by making a copy of the hidden sheet. Then dump in your data and work from there.

Upvotes: 1

Related Questions