Thanks Tom,

How can I use a variable sheet name instead of the index to refer to the sheets?

With ThisWorkbook
set Sh = .Sheets(i)
set Sh1 = .Sheets(1)
For j = 5 TO 10
Sh.Range(Sh.Cells(3,1),Sh.Cells(30,4)).Copy _
Sh1.Range(Sh1.Cells(5,1),sh1.Cells(32,4))

sh.Range(sh.Cells(3,j),sh.Cells(30,j)).Copy _
sh1.Range(sh1.Cells(5,j+2),sh1. Cells(32,j+2)).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Next j
End With
End Sub

The unqualified Cells refers to the activesheet, so you need to qualify them
with the sheet you want.