Problem with copy paste

[paulsolar]

Hi All

I need a little help with a snippet of code please, I think it is almost right but not quite. see below. I want to copy the used range in sheet Template to all sheets after sheet 10 and maintain the formatting and formulae

Cheers

Paul

Sub copy_template()

Dim wsVar As Worksheet

Dim i As Integer
For i = 11 To ThisWorkbook.Worksheets.Count
     ThisWorkbook.Worksheets("Template").UsedRange.Copy ThisWorkbook.Worksheets(i).Range("A1").Paste
Next i

End Sub

 

[Jaafar Tribak]

Maybe something like this :

Sub copy_template()

    Dim i As Integer, oTopLeftCell As Range
    
    With ThisWorkbook.Worksheets("Template").UsedRange
        Set oTopLeftCell = .Cells(1, 1)
        .Copy
    End With
    
    For i = 11 To ThisWorkbook.Worksheets.Count
          ThisWorkbook.Worksheets(i).Range(oTopLeftCell.Address).PasteSpecial xlPasteAll
    Next i

End Sub

 

[paulsolar]

Hi Jaafar

thanks so much, that worked like a dream

 

[Alex Blakenburg]

Depending on whether you later discover columns width to be an issue, you may want to add the line in blue before that PasteSpecial Line.
PS: It needs to be done first otherwise it can mess with alignment of cells using wrap text.

    ThisWorkbook.Worksheets(i).Range(oTopLeftCell.Address).PasteSpecial xlPasteColumnWidths
    ThisWorkbook.Worksheets(i).Range(oTopLeftCell.Address).PasteSpecial xlPasteAll

 

[smozgur]

thanks so much, that worked like a dream

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.