Search code examples
vbaexcelloopscopy-paste

Excel VBA - Copy/Paste range from one sheet to all proceeding sheets

First time asking a question so please let me know if I'm missing anything.

I found this code from another SO post. I'm trying to copy the entire worksheet from "DNU" into each proceeding worksheet. The issue I'm having is that this will paste values but I'm looking to do a regular paste to keep the formatting and formulas. I've tried changing from the "Value" to Copy and Paste but this end up in an error. Any help is appreciated. Thank you.

Here is the code: ~

Dim wsVar As Worksheet

Dim i as Integer
For i = 6 to ThisWorkbook.Worksheets.Count
    ThisWorkbook.Worksheets(i).Range("A1:y200").Value = ThisWorkbook.Worksheets("DNU").Range("A1:Y200").Value
Next i

End Sub
Solution

  • Use Copy() method of Range object 

    Dim wsVar As Worksheet
Dim i as Integer
With ThisWorkbook
    For i = 6 to .Worksheets.Count
        .Worksheets("DNU").Range("A1:Y200").Copy destination:=.Worksheets(i).Range("A1:Y200")
    Next 
End With