Using Cells( ) for Range definition

  • Thread starter Thread starter aalwazeer
  • Start date Start date
A

aalwazeer

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.
 
Thanks Tom,

How can I use a variable sheet name instead of the index to refer to the sheets?
 
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
==========
 
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
 
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
 
Back
Top