looping from one worksheet to the next fails

[CharlieG]

Hi All,

first attempt at looping.. been working with online searches and got very confused.

Basically need to copy a range in Sheet(1) then paste special formats to same range in Sheet(2) before repeating the same for Sheet(3)

Got a feeling am missing something near top of code and the Sheets(2) line of code may be causing code not to loop to Sheet(3)

Option Explicit
Sub CopyPasteFormats()
'
' CopyPasteFormats Macro
'
    Dim WS_Count As Integer
    Dim I As Integer
    WS_Count = Worksheets.Count
    
    For I = 2 To 3
    
    Sheets(1).Activate
    Range("H5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets(2).Select
    Range("H5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    
Next I
    
End Sub

going to use this as one of my reference examples as build my knowledge so any advice massively appreciated as i will not then get confused by too many methodologies.

Thank you.

[Paul_Hossler]

1. You defined the For/Next loop nicely, but never used it

2. Usually no need to select or activate in order to use

3. Some unneeded variables

4. I used a variable rCopy to hold the source

Option Explicit




Sub CopyPasteFormats()


    Dim i As Long                   '   Long is better
    Dim rCopy As Range
    
    Set rCopy = Sheets(1).Range(Sheets(1).Range("H5"), Sheets(1).Range("H5").End(xlDown))
    
    'no need to select of activate
    For i = 2 To 3


        'this is always 1 (one) from the Set rCopy above
        rCopy.Copy
    
    'this is the loop variable
        Sheets(i).Range("H5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False


    Next i
End Sub

[CharlieG]

Huge thank you Paul_Hossler!