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.

Thanks,

If we have varying number of charts, how can we copy them from one
workbook/sheet to another (where every time we have different number
of Charts)

In the example below, I have 8 charts. In other cases it could be
different (e.g. 6, 7, 13, etc.):
=========
Sh.Shapes.Range(Array("Chart 1", "Chart 2", "Chart 3", "Chart 4" _
, "Chart 5", "Chart 6", "Chart 7", "Chart 8")).Select

Selection.Copy

Sh1.Paste
==========
sName = "Sheet1"
set Sh = .Sheets(sName)

Thanks,

but I don't have the charts as sheets. They are embeded in sheets.
"Charts.copy" copies the whole sheet not the charts in the sheet.
If there isn't a simple way to copy a variable number of charts
embeded in a sheet, is there a way to convert the charts from embeded
in a sheet to Chart sheets



Charts.copy

copies all your chart sheets to a new workbook.

Thanks,

but I don't have the charts as sheets. They are embeded in sheets.
"Charts.copy" copies the whole sheet not the charts in the sheet.
If there isn't a simple way to copy a variable number of charts
embeded in a sheet, is there a way to convert the charts from embeded
in a sheet to Chart sheets

"Tom Ogilvy" wrote in message ...
Charts.copy

copies all your chart sheets to a new workbook.

--
Regards,
Tom Ogilvy

wrote in message
om...
Thanks,

If we have varying number of charts, how can we copy them from one
workbook/sheet to another (where every time we have different number
of Charts)

In the example below, I have 8 charts. In other cases it could be
different (e.g. 6, 7, 13, etc.):
=========
Sh.Shapes.Range(Array("Chart 1", "Chart 2", "Chart 3", "Chart 4" _
, "Chart 5", "Chart 6", "Chart 7", "Chart 8")).Select

Selection.Copy

Sh1.Paste
==========
sName = "Sheet1"
set Sh = .Sheets(sName)