Macro for multiple macros in multiple worksheets, same workbook!

[kkaass]

Hi,

I am facing some difficulty with running multiple macros in multiple worksheets. This might be simple, and I searched quite alot, but couldn't find the answer that I am looking for. I am be very grateful if someone can help with this!

The <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code that I tried is not working.

So I have one workbook (that is one Excel document). There are two sheets, Sheet 1 and Sheet 2. I have three Macros, Macro 1, 2 and 3.

I want to run Macro 1 and 2 in Sheet 1 and Macro 3 in Sheet 2. Hence, I want to create a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code which first runs Macro 1 and then Macro 2 in Sheet 1 and then Macro 3 in Sheet 2.

I thought I have to use the worksheet.Activate command but it doesn't seem to be working. I used the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").Activate
Select Case Target.Column
Case Is = 4, 5, 6, 7
Call Macro1
Call Macro2
End Select
Sheets("Sheet2").Activate
Call Macro3
End Sub

Sheet 1 seems to be working with Macro 1 and 2 but then it doesn't go to Sheet 2 and does not run Macro 3.

So basically even by using the above code, the only part that it is running is:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").Activate
Select Case Target.Column
Case Is = 4, 5, 6, 7
Call Macro1
Call Macro2
End Select
End Sub

Maybe there is a problem with the 'module' where I'm creating the above code.
In order to create Macro 1 and Macro 2, I right-clicked on 'Sheet1' in the left hand side panel in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>, insert>module.
Same for Macro 3, I right-clicked on 'Sheet 2' and insert>module.

But for running the above code which combines macros from both sheets, which module should I use, where should I right-click or double-click to create a module, <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Project (Workbook1) or thisworkbook?
What is the correct code that I should be using?

 

[RickXL]

Hi,

It sounds as if you have your macros in the right modules. The event-driven ones go with the sheet and the general ones go into a new Module.

However, I am not entirely sure what you are intending. It looks as if when you make a change to one worksheet, and I am not sure which one that is, all three macros are to be run. Is that correct?

Or do you want a change to worksheet1 to run the macros for worksheet1 (Macro1 and Macro2) and a change to worksheet 2 to run the macro for worksheet 2 (Macro3)?

I never use things like Select and Activate to change sheets any more. You can give the sheet a name and then use that name every time you need to point to a sheet. Even better, you can create the name then put all the code for that sheet into a With/End With construct. See the next example. If you do it like that then Excel can never pick the wrong worksheet or workbook. Note the dots in front of, for instance, .Cells. That means look back to the previous With statement to see what goes on the front.

Sub Demo()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim iRow As Long
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    With ws1
        For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            ' Code mainly processing sheet1 goes here
            .Cells(iRow, "A") = ws2.Cells(i + 3, "C").Value
        Next
    End With
    
    With ws2
        ' More code mainly using Sheet2
    End With

End Sub

 

[kkaass]

Hi,
Thank you so much for your reply. I do apologize for not being very clear. Yes when I make a change column D,E,F or G in Sheet 1 only, Macro 1 and 2 should run in Sheet 1 and Macro 3 should run in Sheet 2, only by making change in Sheet 1.
Yes, I have a name to Sheet1 and Sheet2 previously, I only used Sheet 1 and Sheet 2 for the sake of convenience.

Can you please elaborate a little on this part of the code:
With ws1
For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
' Code mainly processing sheet1 goes here
.Cells(iRow, "A") = ws2.Cells(i + 3, "C").Value
Next
End With

With ws2
' More code mainly using Sheet2
End With

Can I use this code directly as it is, or needs to be modified? What do A and C stand for in this one? There is nothing wrong in using Call for calling macros?

 

[RickXL]

Hi,

The code I posted was an example of the style to use and was not intended for you to use as it is.

If you post your macros I should be able to give some more specific advice.

 

[kkaass]

Hi,
Okay, an example will come in handy later on as well, so thank you for that!

Just a while ago, I used this code, seems to be working fine but yes it uses Activate command and you said its not very effective, but I thought I'll share it, in case anyone would like to have a look.

Sub onetwomacros()
Worksheets("Sheet1name").Activate
Select Case Target.Column
Case Is = 4, 5, 6, 7
Application.EnableEvents = False
Call Macro1
Call Macro 2
Application.EnableEvents = True
End Select
End Sub


Sub threemacro()
Worksheets("Sheet2name").Activate
Application.EnableEvents = False
Call Macro3
Application.EnableEvents = True
End Sub

Here are the macros that I'm using:
Macro 1:
Sub Macro1()
Dim DMUNo As Integer
For DMUNo = 1 To 50
Range("N2") = DMUNo
SolverSolve UserFinish:=True
With Range("I1")
.Offset(DMUNo, 0) = Range("O3")
End With
Next DMUNo
End Sub

Macro 2:
Sub Macro2()
Dim DMUNo As Integer
For DMUNo = 1 To 50
Range("N2") = DMUNo
SolverSolve UserFinish:=True
Range("I" & DMUNo + 1) = Range("O3")
Range("H2:H51").Select
Selection.Copy
Range("Q" & DMUNo + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Next DMUNo
End Sub

Macro 3:
Sub Macro3()
Dim DMUNo As Integer
For DMUNo = 1 To 50
Range("N2") = DMUNo
SolverSolve UserFinish:=True
Range("P4:P7").Select
Selection.Copy
Range("R" & DMUNo + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Next DMUNo
End Sub

So there are similar commands as well in all three above macros, and it might be able to combine them.

First Sheet1name is activated or referred to, then following should be entered (only for Sheet1):
Select Case Target.Column
Case is=4,5,6,7

Then Macro 1 and Macro 2 should be run.

Then Sheet2 be activated/referred to and then Macro 3 should be run.

So in summary, whenever I make a change in Columns D,E,F or G (as indicated by the 'select case' command above) in Sheet1 only, Macro 1 and 2 run in Sheet1 only and then after that, Macro 3 should run in Sheet 2 only.

Thanks in advance!

 

[RickXL]

Hi,

I think this should keep it focused on the right sheets.

Put these macros in a Module:

Sub Macro1()
    Dim DMUNo As Integer
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    With ws
        For DMUNo = 1 To 50
            .Range("N2") = DMUNo
            SolverSolve UserFinish:=True
            .Range("I1").Offset(DMUNo, 0) = .Range("O3")
        Next DMUNo
    End With
End Sub

Sub Macro2()
    Dim DMUNo As Integer
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    With ws
        For DMUNo = 1 To 50
            .Range("N2") = DMUNo
            SolverSolve UserFinish:=True
            .Range("I" & DMUNo + 1) = .Range("O3")
            .Range("H2:H51").Copy
            .Range("Q" & DMUNo + 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Next DMUNo
    End With
End Sub

Sub Macro3()
    Dim DMUNo As Integer
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    With ws
        For DMUNo = 1 To 50
            .Range("N2") = DMUNo
            SolverSolve UserFinish:=True
            .Range("P4:P7").Copy
            .Range("R" & DMUNo + 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Next DMUNo
    End With
End Sub

Put this macro into Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 4, 5, 6, 7
        Call Macro1
        Call Macro2
    End Select
    Application.EnableEvents = True
End Sub

Put this macro into Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Call Macro3
    Application.EnableEvents = True
End Sub

Notes:

Dim ws As Worksheet

This will create a new name, ws, which you can use to refer to the worksheet. Defining it will also make intellisense work so that you will get the dropdown list for worksheet properties and methods in the editor.

Set ws = ThisWorkbook.Worksheets("Sheet1")

This will assign the name ws to the worksheet called Sheet1 in Thisworkbook. As long as you use ws to define worksheets, Excel will always choose the right worksheet and will not be distracted by other workbooks and will ignore the user activating different sheets when macros are running.

With ws

Tells Excel that any references that start with a dot will be treated as references to this worksheet.

 

[kkaass]

Hi RickXL,

Thank you so much for your help, I am very grateful!

Okay, I tried the codes that you just mentioned. But VBA is giving me a compile error for:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Call Macro3
Application.EnableEvents = True
End Sub

The part highlighted is the first line 'Private Sub Worksheet_Change(ByVal Target As Range)' (highlighted in yellow), and 'Call Macro3' is highlighted in blue.
Its not recognizing Macro3 for some reason. I checked, the worksheet is fine, and I used exactly Macro3 in the first module (having all Macro 1, 2 and 3) and this one. But somehow it is giving me a compile error.
The values in Sheet2 do change when Macro3 is run, after Macro1 and 2 are run. So I don't think using application.enableevents has anything do to with this.

Can you please comment why this might be the case, and how can I remove this error?

 

[RickXL]

Hi,

To test your macros as much as I could I commented out the Solver lines - because I don't know what you have set upo.

Everything compiles just fine.

I noticed that, originally, there were two macros that were worksheet change events but when you posted the complete set there were no worksheet change macros.

How do you want it to work?

 

[kkaass]

Hi,
Yes even I can't see any error in the compilation of macros.

Yes, the worksheet change event should apply to both sheets and for all macros. So for instance, whenever I enter a value in say Column D in Sheet1, I want Macro 1 to run in Sheet1 (with worksheet change event because it will change after Macro1 runs), then Macro 2 runs (again with worksheet change event because it will change after Macro 2 runs), and then the code refers to Sheet2 and then runs Macro3 (I think again with worksheet change event, because things will change in Sheet2 after Macro 3 has run).

So maybe I am not using the Worksheetchange event correctly. I think it should be used in both macros that you suggested separately (the Private one).

So should I run them without Worksheetchange, or do you think I should remove the Application.EnableEvents?

 

[kkaass]

Hi,

I tried again, and now the same compilation error is occurring in the other private Sub command for Sheet1, now it is saying that Macro1 is not defined.

So I am assuming, that I am not using the worksheetchange event correctly, or there is something else?

Can you suggest any solution please?