Smart use of .Activate; .Select; .Copy with Sheets(1) va Worksheet

D

Dennis

Using 2003

First, when is it necessary to:

Activate(to ultimately Select)Worksheets(myWorksheet).Select
must I first do .Worksheets(myWorksheet).Activate
then .Worksheets(myWorksheet).Select
then .Worksheets(myWorksheet).Copy

which approach is better?

must I first do .Sheets(myWorksheet).Activate
then .Sheets(myWorksheet).Select
then .Sheets(myWorksheet).Copy

with "Sheets" can I use either an:
Alpha variable or
"Actual W/S Name"
(1) for Sheet1

What about Workbook(myWorkbook).Activate before
myWorkbook.Worksheets(myWorksheet).Activate
myWorkbook.Worksheets(myWorksheet).Select
 
T

tjtjjtjt

with "Sheets" can I use either an:
Alpha variable or
"Actual W/S Name"
(1) for Sheet1
Click to expand...

Have you tried any of these?

In most cases, you can avoid Selecting or Activating altogether.

For instance
Sheets(3).Copy Before:=Sheets(1)
 
D

Dennis

Yes I have. It is the "in most cases" that I am trying to understand. I was
hoping that I could get more background as to why one would use Sheets() vs.
Worksheets().

Thanks Dennis
 
T

tjtjjtjt

The worksheets collection contains only worksheets.
The sheets collection contains worksheets and chartsheets. It also contains
any macro sheets and dialog sheets from xl 4 & xl5.

So, using worksheets is more specific.
 
T

tjtjjtjt

Further clarification
Create a new workbook. Create a chart on its own sheet. Place the ChartSheet
after Sheet1.
Insert these 4 macros into a new module. run them and observe the results.

Sub CountWkShts()
MsgBox Worksheets.Count
End Sub

Sub CountShts()
MsgBox Sheets.Count
End Sub

Sub WkShtsName()
MsgBox Worksheets(3).Name
End Sub

Sub ShtsName()
MsgBox Sheets(3).Name
End Sub
 
D

Dennis

tjtjtjt

The association that I had was "try both until it worked"

Thanks to you I have a much better context with which to investigate and
experiment further.

Dennis

**********************************
 
Top