PDA

View Full Version : Can you speed up my vba that copy and pastes the header amongst worksheets?



e92life
08-17-2017, 01:53 PM
I am using excel 2010. I have a code that pastes the active sheet, right header, onto all other sheets then erases it from the first sheet (as it is a cover page). Then I have it switch previews back and forth on all sheets because the preview kept looking funky without doing this process. I couldn't make this process much easier because we want it to print like a word document but with all the calculation connections between sheets. Therefore there are portrait layouts and landscape within the document. The current macro takes a min and a half to complete. This is gonna be on a template at work so I would like it solid and not so clunky. So if there was a way I could lose some commands/add some and speed it up that would be awesome.


Sub CopyHeaderFooter()
Dim PS As PageSetup
Dim WB As Workbook
Dim WS As Worksheet
Set PS = ActiveSheet.PageSetup
Application.PrintCommunication = False
For Each WB In Workbooks
For Each WS In WB.Worksheets
With WS.PageSetup
.RightHeader = PS.RightHeader
End With
Next
Next
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets

s.Activate
With ActiveWindow
.View = xlPageBreakPreview
.View = xlPageLayoutView
Application.PrintCommunication = True
End With
Sheets("Cover Page").Select
ActiveWindow.SmallScroll Down:=-24
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.RightHeader = ""
End With
Application.PrintCommunication = True
Next s
End Sub

e92life
08-17-2017, 02:11 PM
I also do not need it to run through all of the active workbooks however I am not sure how to do that. I just want it on this workbook. Could that be slowing it down? And I am not very educated on the Application.PrintCommunication = False and Application.PrintCommunication = True