range property of range object
 

I'm working on app where I have to copy data between several books/sheets,
and it's a pain to make sure i'm referencing the correct
source/destination.

I try to set object references to each of the books/sheets I'm working with
and use those to preface the copy/pastes.

While doing that I noticed that the range object has a range property, and
I wanted to know how to use it.

For instance:

Set rngTestRange = oSheet.Cells(6, 1)
Do
Set rngTestRange = rngTestRange.Offset(1, 0)

'check for start of new cost account
If Not IsEmpty(rngTestRange.Value) Then
sAccount = rngTestRange

For x = 1 To oChartSkel.Sheets.Count
'copy chart sheets from skel to this cam's chart notebook
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)
Set oCurrentChartSheet = ActiveSheet
oCurrentChartSheet.Name = sAccount & oChartSkel.Sheets(x).Name

'copy from source to this chart's data
oSheet.Range(Cells(rngTestRange.Row + 1, 4), Cells
(rngTestRange.Row + 16, 21)).Copy
oCurrentChartSheet.Range("B52").PasteSpecial xlPasteValues
Next x

End If

Loop Until rngTestRange.Row = lngLastRow

Instead of

oSheet.Range(Cells(rngTestRange.Row + 1, 4), Cells(rngTestRange.Row + 16,
21)).Copy

Could I have used:

rngTestRange.Range(Cells(rngTestRange.Row + 1, 4), Cells(rngTestRange.Row +
16, 21)).Copy

is the range property from a sheet the same as the range property from a
range?

Thanks,
Woody

range property of range object
 

The Range property of a Range is there to allow you to use addresses relative
to your range's upper left cell. For example, Range(B5:C10).Range("A1") is
cell B5, Range(B5:C10).Range("B1") is cell C5, Range(B5:C10).Range("A2") is
cell B6, etc.

The Worksheet.Range is the entire range on the worksheet, so it uses the
row/column references we all "normally" use.

"Woody" wrote:

I'm working on app where I have to copy data between several books/sheets,
and it's a pain to make sure i'm referencing the correct
source/destination.

I try to set object references to each of the books/sheets I'm working with
and use those to preface the copy/pastes.

While doing that I noticed that the range object has a range property, and
I wanted to know how to use it.

For instance:

Set rngTestRange = oSheet.Cells(6, 1)
Do
Set rngTestRange = rngTestRange.Offset(1, 0)

'check for start of new cost account
If Not IsEmpty(rngTestRange.Value) Then
sAccount = rngTestRange

For x = 1 To oChartSkel.Sheets.Count
'copy chart sheets from skel to this cam's chart notebook
oChartSkel.Sheets(x).Copy After:=oThisMgrChartBook.Sheets(1)
Set oCurrentChartSheet = ActiveSheet
oCurrentChartSheet.Name = sAccount & oChartSkel.Sheets(x).Name

'copy from source to this chart's data
oSheet.Range(Cells(rngTestRange.Row + 1, 4), Cells
(rngTestRange.Row + 16, 21)).Copy
oCurrentChartSheet.Range("B52").PasteSpecial xlPasteValues
Next x

End If

Loop Until rngTestRange.Row = lngLastRow

Instead of

oSheet.Range(Cells(rngTestRange.Row + 1, 4), Cells(rngTestRange.Row + 16,
21)).Copy

Could I have used:

rngTestRange.Range(Cells(rngTestRange.Row + 1, 4), Cells(rngTestRange.Row +
16, 21)).Copy

is the range property from a sheet the same as the range property from a
range?

Thanks,
Woody