Issues combining macros - how can I get a formula macro to run???

[jmhjmh]

Hi,

New to VBA and have been using a mix of youtube tutorials and online guides to try and get solutions here. Will try my best to explain but if its not clear please let me know!

I have a macro working to move rows from tab to tab based on the value entered in column G. There are three options for column G (Active, Won, Removed) and three sheets in the workbook all named the same. This has been working fine and I've been using this:

Sub MoveBasedOnValue()
    Dim xRg As Range
    Dim xCell As Range
    Dim A As Long
    Dim B As Long
    Dim C As Long
    A = Worksheets("Active").UsedRange.Rows.Count
    B = Worksheets("Won").UsedRange.Rows.Count
    If B = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Won").UsedRange) = 0 Then B = 0
    End If
    Set xRg = Worksheets("Active").Range("G1:G" & A)
    On Error Resume Next
    Application.ScreenUpdating = False
    For C = 1 To xRg.Count
        If CStr(xRg(C).Value) = "Won" Then
            xRg(C).EntireRow.Copy Destination:=Worksheets("Won").Range("A" & B + 1)
            xRg(C).EntireRow.Delete
            If CStr(xRg(C).Value) = "Won" Then
                C = C - 1
            End If
            B = B + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

This module is repeated twice per sheet to look at the two options of which tab the corresponding row can move to. I then have this private sub running on sheet 1 (Active) to ensure things work automatically:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Z As Long
    Dim xVal As String
    On Error Resume Next
    Application.EnableEvents = False
    For Z = 1 To Target.Count
        If Not Intersect(Target(Z), Range("G:G")) Is Nothing Then
            If Target(Z).Value > 0 Then
               Call MoveBasedOnValue
               Call MoveBasedOnValue2
            End If
            End If
           
    Next
    Application.EnableEvents = True
End Sub

For the "Active" sheet, there are the two options (MoveBasedOnValue and MBOV2). On sheet two, there is MBOV3 and 4 and so on.

I then want to include a separate macro which populates column J based off entries into columns H and I on the "Active" sheet. I'm using this module:

Sub CalculateDate()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Cells(Rows.Count, "H").End(xlUp).Row
    For i = 2 To LastRow
        If Not IsEmpty(Range("H" & i)) And Not IsEmpty(Range("I" & i)) Then
            If Range("H" & i) = "1. Very High" Then
                Range("J" & i).Value = Range("I" & i).Value + 14
            ElseIf Range("H" & i) = "2. High" Then
                Range("J" & i).Value = Range("I" & i).Value + 28
            ElseIf Range("H" & i) = "3. Medium" Then
                Range("J" & i).Value = Range("I" & i).Value + 42
            ElseIf Range("H" & i) = "4. Low" Then
                Range("J" & i).Value = WorksheetFunction.EDate(Range("I" & i), 2)
            ElseIf Range("H" & i) = "5. Very Low" Then
                Range("J" & i).Value = WorksheetFunction.EDate(Range("I" & i), 6)
            End If
        End If
    Next i
End Sub

This all works and I am able to populate column J when I manually press run macro, but how can I get this to work automatically? Just adding in "Call CalculateDate" doesn't work (no pop up errors, but just doesn't work automatically. How do I need to change the first Private sub for sheet one to combine these two?

I'm not able to just enter a formula and drag down column G as when I would then want to move a row from sheet 2 or 3 back to sheet 1 (Active), it will enter the row where it finds the next blank row - with the formula just being input into the cell, this means anything moves right to the bottom of the sheet. Any alternative solutions which allow for rows to be moved to this tab with column G already populated in that row would be great as well (different method, same outcome).

Appreciate this is a long message so any help would be amazing.

Thanks!

 

[Micron]

A bit much to digest - perhaps I need more coffee!
For one issue, you'd verify that a sub isn't being called by stepping through (F8) the calling code and watch what happens. I'm thinking that's got something to do with your question as to how to automate something. Maybe it runs but doesn't do what you expect.

When a worksheet event has a target parameter, you can use the .Address or .Column or .Row or just the value (default) if that makes any sense to what you need.
Also, your UDF (user defined function) can take parameters (why they call custom code a UDF and lump subs in that definition I don't know, so don't let that confuse you). Maybe you should be passing the sheet name, a value (Z?) or something else such as a Target property to your udf. That is more efficient than having multiple subs that basically do the same thing. It might look something like
Sub MoveBasedOnValue(lngZ As Long)

BTW, none of what you posted is a module. They're all truly procedures, specifically Subs. Why Excel designers called those macros is also beyond me.

Sorry if none of that helps much. After typing it all out I thought about deleting my post as I don't think it will be very helpful. But here goes anyway...