VBA - Multiple macros across worksheets, run with a single button?

[T-rev]

I have 10 work sheets, each with a list of companies all split into sectors, "Energy" "Healthcare" etc.

the data in each of these sheets is queried from a website. so as the query can fail at times and mess up my formating i created a macro that formats the sheets correctly (1 button on each of the sheets) i also have a master sheet with a format button that with the "_Click" Command i would like to "_Click/Run" the other 10 buttons.. i am new to calling procedures and subs in VBA...

how would i go about doing this correctly?

 

[T-rev]

if it helps, this is one of the macros i am trying to get the button to run, this macro is placed in the worksheet and i would only like it to run for the worksheet it is in...

Sub EnergyFormat()

Application.ScreenUpdating = False


With Worksheets("Energy")
'Deletes unwanted rows and columns
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete

Range("L:Z").EntireColumn.Delete
Range("J1").EntireColumn.Delete
Range("G:H").EntireColumn.Delete

Range("A1:H1").Cells.UnMerge
Range("A1:H1").Cells.Merge

'Auto Fits Sizing of columns
    Columns("A:H").Select
    Selection.EntireColumn.AutoFit
    
    
    
    'Sets Borders
    Range("A1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
        'sets inside borders for the whole data range
    Selection.Font.Bold = False
    With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
        'sets outside border for the whole data range
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    'Selects Headers for formatting
    Range("A1:H4").Select
    Selection.Font.Bold = True
    
    'Formats Borders for Headers
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
        'Border for Sector Title
        
        Range("A1").Select
        With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
End With

Range("A1:H1").Cells.UnMerge
Range("A1:H1").Cells.Merge


Application.ScreenUpdating = True

End Sub

 

[VoG]

Try like this

Sub RunAll()
Call EnergyFormat
Call Macro5
'
'and so on
'
End Sub

 

[T-rev]

thanks for the reply VOG, but no that does absolubtly nothing..

the button to call the macros with is on a sheet called "Master" and the first macro i'd like to call is on a sheet called "Energy" these macros are all public subs and all placed in their relative "worksheet Object"

i don't know if that helps but it's baffled me as to why everything i try does diddly squat.

 

[VoG]

If you place all of the code in one or more regular modules then the approach that I suggested should work.

 

[T-rev]

yes i have tried that also... is there a way to call worksheet specific macros? such as

call ("Energy.EnergyFormat")

and since they are all assigned to buttons, do i need the _Click command?
i am assuming that i don't?

am using excel 07 too, not that it should make a difference...

 

[VoG]

You don't need any click type command.

Worksheet modules are for special types of code like event procedures. Everything else should go in a regular module. Then you just Call each one i8n sequence.

 

[T-rev]

ok so i found the problem, was that i had a different name for the button to the name of the Sub, however i did as you suggested copied it all into seperate modules and not the worksheet object and it ran the macro on the master sheet even though the macro specifies

With Worksheets("Energy")

why is this?

i would like it to run on the Energy worksheet, when i click the button on the master list sheet...

 

[VoG]

The quickest fix: change

With Worksheets("Energy")

to

Worksheets("Energy").Select

 

[T-rev]

Works Perfectly Thanks heaps, you're a legend