Select Case Sh.Name

[Pinaceous]

Hi All,

I'm working with an xlsm parent document where the sheet's tab names changes in producing a new children workbook.

My question is is there a way to generically assign a sheet name according to its assigned worksheet#/sheet# in the code below, so I can avoid using over a hundred sheet names in the parent document?

  Sub Workbook_SheetTabName
    Select Case Sh.Name
    Case "Month001": Call Refresh1
    Case "Month002": Call Refresh2

'so on until ...

    Case "Month099": Call Refresh1
    Case "Month100": Call Refresh2
    Case Else: Call Refresh4
    End Select
    
    
End Sub

Please let me know, if you should need an example/further explanation.

Thank you!

Pinaceous

 

[jproffer]

Is it straight on like you show it? In other words, is it basically that odd number sheet names call Refresh1 and even number sheet names call Refresh2? If so there may be another way. I'd have to try it out tomorrow at work.

 

[Pinaceous]

Hi jproffer,

Here's another look at the code in a different way that might help explain this situation that I'm trying to accomplish:

Option Explicit
Sub Workbook_SheetActivate_MonthlyReport(ByVal Sh As Object)-?
    Select Case Sh.Name
    Case "Sheet1": Call October_MTHLYReport
    Case "Sheet5": Call November_MTHLYReport
    Case "Sheet7": Call December_MTHLYReport
    Case "Sheet9": Call January_MTHLYReport
    Case "Sheet11": Call February_MTHLYReport
    Case "Sheet13": Call March_MTHLYReport
    Case "Sheet15": Call April_MTHLYReport
    Case "Sheet17": Call May_MTHLYReport
    Case "Sheet19": Call June_MTHLYReport
    Case "Sheet21": Call July_MTHLYReport
    Case "Sheet23": Call August_MTHLYReport
    Case "Sheet25": Call September_MTHLYReport
    End Select
End Sub

Whereby, I'm in the code, I'm trying to avoid the using the actual "Sheet Name", so I can substitute its actual "Sheet Name" with its generic "Sheet Number".

So, if the "Sheet Name" changes, the code will know what to execute based on its "Sheet Number".

Is this possible? XL always surprises me.

Many thanks,
Paul

 

[My Aswer Is This]

So you have one call per month.
So why would you have more then 100?
You said:

so I can avoid using over a hundred sheet names in the parent document?​

 

[Pinaceous]

I'm also experimenting with this code:

Option Explicit
Sub Workbook_SheetActivate_MonthlyReport()
 Select Case True
    
    Case Worksheets(4): Call October_MTHLYReport
    Case Worksheets(6): Call November_MTHLYReport
    Case Worksheets(8): Call December_MTHLYReport
    Case Worksheets(10): Call January_MTHLYReport
    Case Worksheets(12): Call February_MTHLYReport
    Case Worksheets(14): Call March_MTHLYReport
    Case Worksheets(16): Call April_MTHLYReport
    Case Worksheets(18): Call May_MTHLYReport
    Case Worksheets(20): Call June_MTHLYReport
    Case Worksheets(22): Call July_MTHLYReport
    Case Worksheets(24): Call August_MTHLYReport
    Case Worksheets(26): Call September_MTHLYReport
    End Select
End Sub

Whereby, here in the code, I'm trying to avoid the using the actual "Sheet Name", so I'm substituting its actual "Sheet Name" with its generic "Worksheet Number".

Same ides, where, if the "Sheet Name" changes, the code will know what to execute based on its "Worksheet Number".

 

[Pinaceous]

So you have one call per month.
So why would you have more then 100?
You said:

so I can avoid using over a hundred sheet names in the parent document?​

Because the parent document has many sheet names in designating its children.

So, the sheet name for each child's can be:

________________________
AA October ... AA September

... so on ...

AZ October ... AZ September
________________________

... so on ...
________________________
BA October ... BA September

... so on ...

BZ October ... BZ September
_______________________


& so on.

This part is not important. The idea avoid the using the actual "Sheet Name", with a generic "Sheet Number" or a "Worksheet Number" is.