Help getting a worksheet name from another Workbook

[DChambers]

I have written a procedure that resides in 'thisworkbook' of a Reference Workbook that asks for a workbook name, the worksheet name and the column to be worked on. It then opens the book, replaces the abreviations in this column with the full name and saves it.

My problems is that I have to open each workbook, find the correct worksheet with the column - write this down and run the above procedure.

I know how to open a workbook, get a column, but cannot figure out how to navigate to the correct worksheet and get its name.

Any Ideas?

 

[DonkeyOte]

Not sure I follow -- do you mean loop all worksheets to test names, ie along the lines of:

Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
   Select Case UCASE(ws.Name)
      Case "SHEET1"
      Case "SHEET2"
      Case Else
   End Select
Next ws

or to select a sheet if you have the name already ?

Sheets("Sheet1").Select

 

[DChambers]

I was thinking the procedure should do something like this:

Ask for, open and display on the screen the workbook (I can already do this part).

Using an Input box or some such user input do this:
Title: using your mouse find the sheet that has the abreviations then click 'OK'

This box would then stay on the screen until I find the right sheet and click 'OK' then it would get the active sheet name and pass it back as a string.

 

[Norie]

Darrell

You really shouldn't rely on using ActiveSheet.

Is there anyway to determine the worksheet other than user input?

 

[DChambers]

Hi Norie,

Not that I can think of ...
If you are looking at the sheet on the screen isn't it the active sheet?
Couldn't I use activesheet.name?
If this is true perhaps there would be a way to cycle through the sheets?

BTW why not use activesheet?