Dynamic Worksheet Names
 

I am building a spreadsheet that uses another workbook as input data. The
workbook is created by a software package and names its worksheets as follows:
1. Function; 2. Event; 3. Position etc etc.

Here is the rub, for reasons that I cannot control, the software package
sometimes names the worksheets like the manner above, and sometimes like this:
1. Event; 2. Position; 3. Function etc or any combination of these
names.
The information in the "Function" worksheets are all simillar despite the
varying "nn. " prefix.

There are as many as 15 worksheets in each workbook.

Here is what I think I need:
a function that outputs the name of the [nth] worksheet listed in a
specified workbook.

Failing that, I would like that ability to reference worksheet data without
knowing the worksheet name in advance.

Thanks in advance.

If I understand your dilemma correctly, it would work for you if you run a
macro like this on the output file before opening the linked file:
Dim sName As String
Dim i As Integer
For i = 1 To ActiveWorkbook.Sheets.Count
Sheets(i).Select
sName = Right(ActiveWorkbook.ActiveSheet.Name,
Len(ActiveWorkbook.ActiveSheet.Name) - InStr(ActiveWorkbook.ActiveSheet.Name,
".") - 1)
ActiveWorkbook.ActiveSheet.Name = sName
Next i

This would rename the sheets by removing the number, the period and the
space in fron of "Function", "Event", etc.

Chris

"JClub" wrote:

I am building a spreadsheet that uses another workbook as input data. The
workbook is created by a software package and names its worksheets as follows:
1. Function; 2. Event; 3. Position etc etc.

Here is the rub, for reasons that I cannot control, the software package
sometimes names the worksheets like the manner above, and sometimes like this:
1. Event; 2. Position; 3. Function etc or any combination of these
names.
The information in the "Function" worksheets are all simillar despite the
varying "nn. " prefix.

There are as many as 15 worksheets in each workbook.

Here is what I think I need:
a function that outputs the name of the [nth] worksheet listed in a
specified workbook.

Failing that, I would like that ability to reference worksheet data without
knowing the worksheet name in advance.

Thanks in advance.