I am trying to write a code using the Cells() for range defintion but
it didn't work. It only works when I use the regular defition [i.e.
Range("A3:D30") instead of Range(Cells(3,1),Cells(30,4))]

Here is what I tried to write:

Sub CopyToSheet1 ()
Dim i As Integer

i = InputBox("Enter Sheet Number (1):")

With ThisWorkbook
For j = 5 TO 10
.Sheets(i).Range(Cells(3,1),Cells(30,4)).Copy
Sheets("Sheet1").Range(Cells(5,1),Cells(32,4))

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

Next j
End With
End Sub

Sub CopyToSheet1 ()
Dim i As Integer
Dim sh as worksheet
Dim sh1 as worksheet

i = InputBox("Enter Sheet Number (1):")

With ThisWorkbook
set Sh = .Sheets(i)
set Sh1 = .Sheets("Sheet1")
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.

--
Regards,
Tom Ogilvy

wrote in message
om...
I am trying to write a code using the Cells() for range defintion but
it didn't work. It only works when I use the regular defition [i.e.
Range("A3:D30") instead of Range(Cells(3,1),Cells(30,4))]

Here is what I tried to write:

Sub CopyToSheet1 ()
Dim i As Integer

i = InputBox("Enter Sheet Number (1):")

With ThisWorkbook
For j = 5 TO 10
.Sheets(i).Range(Cells(3,1),Cells(30,4)).Copy
Sheets("Sheet1").Range(Cells(5,1),Cells(32,4))

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

Next j
End With
End Sub

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.