Pagesetup.printarea is sooo slow

[kdogg3000]

Hi all, long time reader, 1st time poster...

Any way i can speed this up? I am copying an entire template sheet (by cells-see reason below) over to a new sheet, and then copying the printarea as well. My code runs through a lot of different functions and routines, but it seems to take a few (3~4) seconds for the printarea to set. (the rest of my code takes < 1 sec /per page it makes).

I've tried copying and renaming the template (which bypasses the problem), but then i am limited to only copying 50 sheets in one excel session (MS capabilities ) , and need the program to be robust enough for that not to occur. Any suggestions?

My current code sets a string to the template application.pagesetup.printarea, and then sets my new sheet up to the string.

Sub testing()
Dim s As String
s = ActiveSheet.PageSetup.printArea
sheets("XXX").PageSetup.printArea = s
End Sub

Anyone run into this or have any solutions?

THANKS!

 

[DominicB]

Good evening kdogg3000

Welcome to the board!

Unfortunately, as you have found, copying settings across from one sheet to another does take quite a bit of time and the best thing is ... there's not much you can do about it

All you could do really is flash up a message in a userform or in the caption area telling the user that the settings are being copied and to be patient.

HTH

DominicB

 

[Marc]

I was just looking at the Help File.

Would the following line allow you to achieve your desired results? It seems to me you would just need to select a cell within your print region just prior to running the line.

ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address