Reference an unknown sheet

Question

I have 2 files open.  One file has the code.  The other file's name is in the variable OrigWB.  That file has only one sheet.  I don't know the name of that sheet. How can I reference that sheet so that I can work with it? Specifically, I want to use the statement With Workbooks(OrigWB).xxxxxxxx where the x's refer to that sheet.  Alternatively, is there a way to find the name of that sheet?  Thanks for your time.  Otto

Answer 1

And, to follow up, if you actually needed to know that worksheet's name...

TheSheetName = Workbooks(OrigWB).Worksheets(1).Name

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 2

Since there is only one sheet, I would think you could reference the first index...

With Workbooks(OrigWB).Worksheets(1)

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

Am 13.01.2011 18:54, schrieb Otto Moehrbach:

I have 2 files open.  One file has the code.  The other file's name is in the variable OrigWB.  That file has only one sheet.  I don't know the name of that sheet. How can I reference that sheet so that I can work with it? Specifically, I want to use the statement With Workbooks(OrigWB).xxxxxxxx where the x's refer to that sheet.  Alternatively, is there a way to find the name of that sheet?  Thanks for your time.  Otto

Every collection in the application accepts numbers and strings as parameters, i.E.

WorksBooks(1) is always the first workbook

WorksBooks("MyFile.xls") is a specific workbook

Sheets(1) is always the first sheet

Sheets("MySheet") is a specific sheet

Cells(1,1) or Range("A1") is always the first cell

Range("MyNamedRange") is a specific range

OrigWB.Sheets(1).Range("A1") is the first cell in that book.

On the other hand you can determine the sheet from a range with the Parent property

Range("A1").Parent.Name is the name of the sheet

Range("A1").Parent.Parent.Name is the name of the workbook

Range("A1").Parent.Parent.Parent.Name is the name of the application

or get the names from the objects

Sheets(1).Name is the name of the sheet

WorkBooks(1).Name is the name of the workbook

Application.name is the name of the application

Andreas.

Answer 4

Hi,

If it has one sheet then the index of that sheet will be 1 so you can use the index or get the sheet name using the index

Get the name

shtname = Workbooks(OrigWB).Worksheets(1).Name

Read it using INDEX

Myval = Workbooks(OrigWB).Worksheets(1).Range("A1").Value

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 5

Visible Sheets are indexed, so the first displayed sheet is worksheets(1)...

With Workbooks(OrigWB).WorkSheets(1)

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.