VBA: Combine Macro

[datastudent]

Hi,

How do I combine all my Macro so that I would only need to click one time? Please note that my Macro are all just reconstruction of data in multiple sheets. I have Sheet1, Sheet2, Sheet3 and Sheet4.

 

[Joe4]

You can call Macros from other Macros. Many people like to break them apart into smaller procedures, to keep it shorter/cleaner, or to make certain parts easy to re-use.

So, if you have Macro1, Macro2, Macro3, you could call them one after the other like this:

Sub MainMacro()
    Macro1
    Macro2
    Macro3
End Sub

 

[datastudent]

You can call Macros from other Macros. Many people like to break them apart into smaller procedures, to keep it shorter/cleaner, or to make certain parts easy to re-use.

So, if you have Macro1, Macro2, Macro3, you could call them one after the other like this:

Sub MainMacro()
    Macro1
    Macro2
    Macro3
End Sub

Call was my first thought too and I did it but for some reason its not doing what I expected that's why I asked here maybe there's another way or maybe I'm doing something wrong?

Macro Assignment
Sheet1 - Macro1
Sheet2 - Macro2
Sheet3 - Macro3
Sheet4 - Macro4

So here's what I'm doing, I'm in Sheet1 then I click my Call Macro. What's happening is that all the command in all the Macro is happening in Sheet1 only or in the sheet that I'm in.

 

[Joe4]

If you do not have code at the top of each procedure telling it what sheet to run against, it will default to run against what ever the Active Sheet is at the time that Macro is called.

Also, if you have your code within the Sheet modules, I would recommend creating a General Module, and moving all your procedures there, so they are accessible from every sheet.

 

[mumps]

Perhaps you can post the code from one of your macros so we can get an idea of what you are trying to so.

 

[datastudent]

If you do not have code at the top of each procedure telling it what sheet to run against, it will default to run against what ever the Active Sheet is at the time that Macro is called.

Also, if you have your code within the Sheet modules, I would recommend creating a General Module, and moving all your procedures there, so they are accessible from every sheet.

I try this one but the code still runs on the sheet where I'm in and not on Sheet4.

Sub Macro4()
'
Dim ws As Worksheet

Set ws = Sheets("Sheet4")
With ws
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[1])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A20")
Range("A2:A20").Select

End With

End Sub

 

[Joe4]

That is not how you write a With statement.
All your ranges within your With need to start with a period (.) to be applied to the With range.
Overwise, it reverts to the Active Sheet again!

Try:

Sub Macro4()
'
Dim ws As Worksheet

Set ws = Sheets("Sheet4")
With ws
    .Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A2").FormulaR1C1 = "=UPPER(RC[1])"
    .Range("A2").AutoFill Destination:=.Range("A2:A20")
End With

End Sub

 

[mumps]

Try:

Sub Macro4()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet4")
    With ws
        .Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("A2").FormulaR1C1 = "=UPPER(RC[1])"
        .Range("A2").AutoFill Destination:=Range("A2:A20")
    End With
End Sub

 

[Joe4]

Mumps, shouldn't that last line in the With block look like this:

        .Range("A2").AutoFill Destination:=.Range("A2:A20")

instead of this:

        .Range("A2").AutoFill Destination:=Range("A2:A20")

like I showed in my code (note the period in front of the second Range reference)?

 

[mumps]

It sure does. Thanks for picking up on that.