Worksheet names

[John]

Can any one please help me with some vba code that will identify the names of the worksheets in a workbook and then assign a different macro to each one. There are a number of sheets that are hidden and when the work book is used only those that are required are "unhidden" It is to these sheets that I want to assign macros.

Thanks

 

[Ivan F Moala]

On 2002-03-21 20:30, John wrote:
Can any one please help me with some vba code that will identify the names of the worksheets in a workbook and then assign a different macro to each one. There are a number of sheets that are hidden and when the work book is used only those that are required are "unhidden" It is to these sheets that I want to assign macros.

Thanks

To get Sheet Names;
Sub sheetNames()
Dim Sh As Worksheet
Dim i As Integer

For Each Sh In ThisWorkbook.Sheets
Cells(i + 1, 1) = Sh.Name
i = i + 1
Next
End Sub


Unsure about what you mean about assign macros to these sheets ??


Ivan

 

[John]

Thanks Ivan.

I want to call or run a macro. Since each sheet has different data on it I need to use the name of the sheet to call the macro.
for example if the sheet name is data one I want to call Data_1

Regards

 

[Colo]

Hi! Do you mean something like this?
Please execute "Test".

Sub Test()
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Visible = xlSheetVisible Then
Application.Run ThisWorkbook.Name & "!" & sh.Name
End If
Next
End Sub

Sub Data_1()
MsgBox "Data_1 has been executed"
End Sub