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.
Copy sheet including shapes in header and footer regions
- Thread is marked as Resolved.
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
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)
.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
runtime error at line ".LeftHeaderPicture = WsMaster.PageSetup.LeftHeaderPicture"
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.
Thanks I finally got round to look at this again. I took a completely different approach! Your link gave a better idea, Thanks
Don’t have an account yet? Register yourself now and be a part of our community!