Using Cells( ) for Range definition
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
|