Copy sheet including shapes in header and footer regions

ape · Dec 15th 2018

I have a master sheet with some data and shapes both in header and footer. I want to copy the entire sheet to a newly created sheet including shapes(images) that are in the header and footer regions (like for like). Can anyone help?
Thanks in advance.

bigfoot52277 · Dec 18th 2018

Reference to: https://docs.microsoft.com/en-us/off...xcel.pagesetup, you can copy the page and contents over to a new sheet using:

Code

Sub CopySheet()
     Dim thiswb As Workbook, wb As Workbook
     Dim newtab As Worksheet
     Set thiswb = ThisWorkbook
     Set wb = Workbooks.Add()
     With thiswb
        .Sheets("Sheet1").Copy After:=wb.Sheets(wb.Sheets.Count)
         Set newtab = wb.Sheets(wb.Sheets.Count - 1)
     End With
     With newtab
         sheetname = InputBox("Enter name of this Worksheet")
        .Name = sheetname
     End With
     With newtab.PageSetup
         .LeftHeader = Sheets("Sheet1").PageSetup.LeftHeader
         .CenterHeader = Sheets("Sheet1").PageSetup.CenterHeader
         .RightHeader = Sheets("Sheet1").PageSetup.RightHeader
         .LeftFooter = Sheets("Sheet1").PageSetup.LeftFooter
         .CenterFooter = Sheets("Sheet1").PageSetup.CenterFooter
         .RightFooter = Sheets("Sheet1").PageSetup.RightFooter
         .LeftHeaderPicture = Sheet("Sheet1").PageSet.LeftHeaderPicture
         'and so on and so on until all formatting is as sheet1
     End With
 End Sub

Display More

Untested because I am working on Linux right now and don't have access to Excel.

ape · Dec 18th 2018

Thanks for the reply. I have a code which generates the a new page page further up in the program. Sheet name is then passed on to be filled with logos headings etc. I tried a similar code but I keep getting the error below which I can't get rid of!

"runtime erroe 438 Object doesn't support this property or method"
The picture is in jpeg format

code;

Private Sub CopyMasterSheet(SheetName As String)
'Update header, footer and cells from page "Master"
Dim WsMaster As Worksheet
Set WsMaster = Sheets("Master")
Set Ws = Sheets(SheetName)
WsMaster.Cells.Copy
Ws.Paste
With Ws.PageSetup
.LeftFooter = WsMaster.PageSetup.LeftFooter
.CenterFooter = WsMaster.PageSetup.CenterFooter
.RightFooter = WsMaster.PageSetup.RightFooter
.CenterHeader = WsMaster.PageSetup.CenterHeader
.LeftHeaderPicture = WsMaster.PageSetup.LeftHeaderPicture
.RightHeader = WsMaster.PageSetup.RightHeader
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub

ape · Dec 18th 2018

runtime error at line ".LeftHeaderPicture = WsMaster.PageSetup.LeftHeaderPicture"

bigfoot52277 · Dec 19th 2018

Hey Ape, Best I can tell you are going to have to have access to the original image you can still set it in the left header or maybe I am missing something kinda new to VBA myself just trying to help out. You can check out https://docs.microsoft.com/en-…gesetup.leftheaderpicture maybe that will lead you in the right direction. I also see that .PageSetup.LeftHeader = "&G" has to be set in order for the image to be visible on the page. Also, it could be an option added in a later version of Excel I have seen several properties that are like that, not available before say Excel 2010.

ape · Dec 24th 2018

Thanks I finally got round to look at this again. I took a completely different approach! Your link gave a better idea, Thanks

Copy sheet including shapes in header and footer regions

Participate now!

Share