running a macro on more than one sheet

[tonywatsonhelp]

Hi Everyone

I have a workbook with loads of sheets in it

I have one sheet call "Tab Names" which has a list of some of the tabs (40) in Range A2:A42

I have a macro called "Banana"

I want another macro that when in run it will go to each tab in the list and run the macro "banana" then got to the next etc.

any ideas how I can do this?

Thanks

Tony

 

[Sinon]

Try something like:

Sub macro()
Dim i As Integer
Dim Name as string
Dim Last As Integer

ThisWorkbook.Sheets("Tab Names").Activate

Last = Cells(Rows.Count, "A").End(xlUp).Row
    
For i = Last To 2 Step -1
     Name = Range("A" & i).Value
     Sheets(Name).Activate 
     'Insert banana code for what you want to do, this activates the sheet only and you might want something else
Next i

End Sub

If what you want is to apply the banana code in every sheet then you can get away with

Dim ws as Worksheet

For Each ws in ThisWorkbook
'Banana Code
Next ws

 

[frank_AL]

Tony,

This code will set a variable to the value in cells A2:A42 then call your Banana code module. You will need to make any adjustments in that module to insure it is acting on the current worksheet as set in the code below. You might want to consider passing the ws2 variable value to the Banana code module

Sub RunBanana()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim i As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws2name As String


Set ws1 = Worksheets("Tab Names")
For i = 2 To 42
ws2name = Worksheets("Tab Names").Cells(i, "A").Text
Set ws2 = wb.Sheets(ws2name)
Call Banana
Next i


End Sub

 

[Fluff]

I have one sheet call "Tab Names" which has a list of some of the tabs (40) in Range A2:A42

If you have some of the 40 names in a 40 cell range, Does this mean that some of the cells in A2:A42 are blank?

 

[frank_AL]

If you have some of the 40 names in a 40 cell range, Does this mean that some of the cells in A2:A42 are blank?

To Fluff's point I have added a check to ensure if the cell is not blank before proceeding.

[COLOR=#333333]Sub RunBanana()[/COLOR]
[COLOR=#333333]Dim wb As Workbook: Set wb = ThisWorkbook[/COLOR]
[COLOR=#333333]Dim i As Integer[/COLOR]
[COLOR=#333333]Dim ws1 As Worksheet[/COLOR]
[COLOR=#333333]Dim ws2 As Worksheet[/COLOR]
[COLOR=#333333]Dim ws2name As String[/COLOR]


[COLOR=#333333]Set ws1 = Worksheets("Tab Names")[/COLOR]
[COLOR=#333333]For i = 2 To 42
[/COLOR]    If [COLOR=#333333]Worksheets("Tab Names").Cells(i, "A").Text <> ""[/COLOR]
[COLOR=#333333]        ws2name = Worksheets("Tab Names").Cells(i, "A").Text[/COLOR]
[COLOR=#333333]        Set ws2 = wb.Sheets(ws2name)[/COLOR]
[COLOR=#333333]        Call Banana
[/COLOR]    End If
[COLOR=#333333]Next i[/COLOR]


[COLOR=#333333]End Sub[/COLOR]