How to paste all including shapes and column widths in Excel VBA

user_11815111 0
Viewed 223

I am using the below code to paste a row onto each new sheet. I am trying to get it to paste all, including shapes and column widths. ActiveSheet.Paste includes Shapes but not Column Widths. I have tried Sh.Range("1:1").PasteSpecial xlPasteAll but this pastes neither the shapes or column widths.

I know I need to incorporate xlPasteColumnwidths but sure how to do this with ActiveSheet.Paste.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Sheets("Template").Range("1:1").Copy
    Sh.Range("1:1").Select
    ActiveSheet.Paste
        
End Sub
vba excel
2 Answers

Is this what you are trying?

Dim wsInput As Worksheet, wsOutput As Worksheet

Set wsInput = Sheets("Template")
Set wsOutput = Sheets("Whatever") '<~~ Change as applicable

wsInput.Rows(1).Copy wsOutput.Rows(1)

wsInput.Rows(1).Copy
wsOutput.Rows(1).PasteSpecial Paste:=xlPasteColumnWidths, _
                                     Operation:=xlNone, _
                                     SkipBlanks:=False, _
                                     Transpose:=False

I am trying to make it work on creation of a new sheet. My knowledge of vba is zero so I'm sure it is not the correct way, however I have managed to get it work. I will add it as an answer. Please comment if there is a better way! – aye cee 2 mins ago

If you want to perform the copy paste when a new sheet is added then you need to make slight amends to the above code as shown below.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim wsInput As Worksheet
    
    Set wsInput = Sheets("Template")
    
    wsInput.Rows(1).Copy Sh.Rows(1)
    
    wsInput.Rows(1).Copy
    sh.Rows(1).PasteSpecial Paste:=xlPasteColumnWidths, _
                                         Operation:=xlNone, _
                                         SkipBlanks:=False, _
                                         Transpose:=False
End Sub
user_11405790

I have accepted Siddaharth's answer above and it works very well. Just thought I would post the solution I had come up with prior to that. I have very little vba knowledge, so this may be inefficient but here it is.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Sheets("Template").Range("1:1").Copy
    Sh.Range("1:1").Select
    ActiveSheet.Paste
    Sh.Range("1:1").PasteSpecial Paste:=xlPasteColumnWidths 
End Sub
user_118151110
Related