Run multiple VBA codes on one sheet

[mattjpep]

I have two sets of vba code. Is it possible to run them on the same sheet either simultaneously or one after the other?

Code 1:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 
 Application.ScreenUpdating = False
 
    Dim rng As Range, r As Range
    
    Set rng = Intersect(Range("AB11:BW224"), Target) 'Range as posted, adjust for real range
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    For Each r In rng
        If r.Value = "" Then r.FormulaR1C1 = "=HLOOKUP(R8C,Table5[[#All],[MON IN]:[FRI OUT]],ROW()-9,0)"
    Next r
    Application.EnableEvents = True


End Sub

Code 2:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$M$2" Then 'check if cell M2 was changed
        Dim myRange As Range
        Dim myInt As Long
        Set myRange = Range("AD9")
        myInt = 2
        
        Do Until myRange.Value >= Target.Value 'go through dates until the current date is reached
            If myRange.Value < Target.Value Then
                Do Until IsEmpty(myRange.Offset(myInt, 0)) 'go down columns and replace with values
                    myRange.Offset(myInt, 0).Value = myRange.Offset(myInt, 0).Value
                    myInt = myInt + 1
                Loop
            myInt = 2
            End If
            Set myRange = myRange.Offset(0, 1)
        Loop
        
    End If
End Sub

 

[starl]

You can't run them at the same time.

Move the code into standard modules and rename them as subs (you can't have events in standard modules). Then create a main sub that calls them in the order you want.

If you still need the events to work, then have the events call the subs you create.

Note that you'll have to rewrite the subs since you no longer will have a Target argument.

 

[mattjpep]

Thank you for your help. I'm not sure how to change the code if I were to move it to a module and call it in. If possible, I'd like to leave code 1 in the current sheet and move code 2 to a module and use it only when pressing specific keystrokes.

 

[starl]

You mean you want to assign a keyboard shortcut to code 2?

1. Insert a Module in your workbook (option under Insert menu)
2. Code 2 needs to be rewritten if it's no longer an event. You'll need to delete the existing version. Paste the following in the new module you inserted

Sub ReplaceM2()
Dim myRange As Range
Dim myInt As Long

If ActiveCell.Address = "$M$2" Then    'check if cell M2 was changed
    Set myRange = Range("AD9")
    myInt = 2

    Do Until myRange.Value >= ActiveCell.Value    'go through dates until the current date is reached
        If myRange.Value < ActiveCell.Value Then
            Do Until IsEmpty(myRange.Offset(myInt, 0))    'go down columns and replace with values
                myRange.Offset(myInt, 0).Value = myRange.Offset(myInt, 0).Value
                myInt = myInt + 1
            Loop
            myInt = 2
        End If
        Set myRange = myRange.Offset(0, 1)
    Loop

End If
End Sub

3. Assign a keyboard shortcut to the sub (via the Macros dialog on the View tab)

I've written it so that it only works when M2 is selected. Note that it's not sheet specific - it'll run on ANY SHEET in the workbook

 

[mattjpep]

Thank you for the help. I really appreciate it. How would I make it sheet specific, assuming the sheet name is "Master Data"?

 

[starl]

You basically specify the parent object (the sheet) for each child (cell). Since, in your case, it's all one sheet, we can do it simply with a With statement. The use of a period (.) before each child (Range) tells the code to look at the most previous With statement for the parent. I added a line to ensure the ActiveSheet is Master Data - else, the code does nothing.

Now, if you want it to run no matter what sheet is active and what cell is selected, that can also be done.

Sub ReplaceM2()
Dim myRange As Range
Dim myInt As Long

If ActiveSheet.Name <> "Master Data" Then Exit Sub

With Worksheets("Master Data")
    If ActiveCell.Address = "$M$2" Then    'check if cell M2 was changed
        Set myRange = .Range("AD9") 'the period in front of Range tell the code to look at With statement for the specific sheet
        myInt = 2
    
        Do Until myRange.Value >= ActiveCell.Value    'go through dates until the current date is reached
            If myRange.Value < ActiveCell.Value Then
                Do Until IsEmpty(myRange.Offset(myInt, 0))    'go down columns and replace with values
                    myRange.Offset(myInt, 0).Value = myRange.Offset(myInt, 0).Value
                    myInt = myInt + 1
                Loop
                myInt = 2
            End If
            Set myRange = myRange.Offset(0, 1)
        Loop
    
    End If
End With
End Sub

 

[mattjpep]

I'm not sure what the problem is, but the code won't run. I assigned a letter to it, but even when I try to run it from the macros menu, I get nothing. Any ideas?

 

[starl]

Not without seeing the file.
The active sheet must be called "Master Data" and you must have cell M2 selected.