Hi Rod

The best approach, in my opinion, when referencing a worksheet object is to
use the object name. This is Sheet1, Sheet2 etc for sheets and is created
when the sheet is added. You can change these in the VB editor in the
properties window. These names can only be changed here and it doesn't
matter what the sheet name is.

For example

The first worksheet created will have the object name Sheet1 regardless of
where it is in the actual order. It may have a worksheet name (shown on the
tab) of Data. I usually rename (using the name property of the object) this
sheet wsData and then reference the object directly. e.g.
wsData Range("a1").copy

Hope this helps.
Nick

"HotRod" wrote in message
...
Jim
Just a question since this really has been what I'm wondering

1) If I use Sheet1 instead of Worksheet("SheetName") are all of the sheets
labeled sequentialy from Sheet1 - Sheet100, and what happens if someone
changes the order of the worksheets?

2) I've now replaced the code
Application.Worksheet("SheetName").Range() etc.

with

Rng.Range() etc.

In the code below would it make more sense to create to Range Objects?

'COPY MAINSHEET TO
WORKSHEET --------------------------------------------------------------------------------------------------
'Select Range on first Sheet and then copy Values only.

Set Rng = Worksheets(SheetName).UsedRange.Rows

'Clear old sheet first
Application.Worksheets(SheetName2).Range("A" & WorkSheet_First_Row,
"Z55500").Value = ""

'COPY DATA
Application.Worksheets(SheetName2).Range("A2", "G" & (Rng.Rows.Count -
(First_Row - WorkSheet_First_Row))).Value = _
Application.Worksheets(SheetName).Range("A" & First_Row, "G" &
Rng.Rows.Count).Value