Refer to a lone sheet

[Otto Moehrbach]

Excel XP & Win XP
When a wb has only one sheet, the code name of that one sheet may not be
"Sheet1" if other sheets have been deleted.
If that wb is the active wb I can refer to that one sheet as "ActiveSheet".
My question comes when that workbook is not the active wb and I want to
paste into that one sheet. How do I refer to that one sheet to paste into
it, as in:
With wb.Sheets(???)
.range("A1").paste
End with
Thanks for your time. Otto

 

[ker_01]

With wb.worksheets(1)


From the help file:
---------------------
Referring to Sheets by Index Number
See AlsoSpecificsAn index number is a sequential number assigned to a sheet,
based on the position of its sheet tab (counting from the left) among sheets
of the same type. The following procedure uses the Worksheets property to
activate worksheet one in the active workbook.

Sub FirstOne()
Worksheets(1).Activate
End Sub

If you want to work with all types of sheets (worksheets, charts, modules,
and dialog sheets), use the Sheets property. The following procedure
activates sheet four in the workbook.

Sub FourthOne()
Sheets(4).Activate
End Sub

Note The index order can change if you move, add, or delete sheets.

 

[Dave Peterson]

with wb.worksheets(1)
or
with wb.sheets(1)

wb.sheets(1) is the leftmost sheet.

wb.worksheets(1) is the leftmost worksheet (chart sheets, macro sheets, dialog
sheets, ... could be further left).

 

[Otto Moehrbach]

Ker, Dave
Thanks for the help. Otto

 

[Patrick Molloy]

you've been given two alternatives, but activesheet still works even if the
workbook itself isn't teh active workbook.
you can't SELECT a cell in this activesheet if the book isn't active,but you
generally don't neeed cells to be selected in order to use them


so open a new excel instance, and add this code to book1
Sub test()
Workbooks("book1").ActiveSheet.Range("A1") = 1
End Sub

add a couple more books so that book2 or book3 is active. the code still
runs ok

 

[Otto Moehrbach]

Patrick
Thanks for that. I didn't know that would work. I always thought
"ActiveSheet" could only be used with the active workbook. Otto