Copy sheet including shapes in header and footer regions

  • 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.

  • Reference to:, you can copy the page and contents over to a new sheet using:

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

  • 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


    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)
    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

  • 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…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.

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!