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!