join models

[Paul_Hossler]

Took a quick look

I'd suggest making things more modular

In my example, the Main sub calls the lower level subs and for clarity eacn lower level sub is on a separate sheet

The user just needs to run "Main" and it calls the others

Not sure about the rest you want to run, but look at this version

After I deleted the code that is in the other modules, mod_50_More has the remainder


Your main macro could look something like this

Option Explicit


Sub Main2()

    Dim wsData As Worksheet
    Dim bDoNegatives As Boolean

    'make sure we're on the right sheet
    If ActiveSheet.Name = "Data" Then
        Call MsgBox("Can't do it on the Data sheet. Pick another", vbCritical + vbOKOnly, "Format Macro")
        Exit Sub
    Else
        If MsgBox("Do you want to run the format macro on this sheet?", vbQuestion + vbYesNo, "Format Macro") = vbNo Then
            Exit Sub
        End If
    End If
    
    'see if we're going to do the negative numbers
    bDoNegatives = (MsgBox("Do you want to change the negative prices?", vbQuestion + vbYesNo, "Format Macro") = vbYes)

    'setup and init
    Application.ScreenUpdating = False
    ActiveSheet.Name = "Input"
    Set wsData = Worksheets("Data")

    Call ReplaceAllSheets(wsData.Range("A1"))
    Call ReplaceAllSheets(wsData.Range("D1"))
    Call ReplaceAllSheets(wsData.Range("G1"))
    
    Call addClassReturns
   
     If bDoNegatives Then Call Negative
    
    Call Template
    Call AddDiscountReturns
    Call More                       '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< the rest of the code mod_50
    
    'cleanup
    With Worksheets("Input")
        .Range("A1").Value = "Invoice Date"
        .Range("B1").Value = "Invoice Number"
        .Range("C1").Value = "Account Name"
        .Range("D1").Value = "Item"
        .Range("E1").Value = "Qty"
        
        .Range("H1").Value = "Discount Price"
        .Range("I1").Value = "line class"
        .Range("J1").Value = "class"
        .Range("K1").Value = "template"
    End With
    Application.ScreenUpdating = True
    Call MsgBox("Format macro completed", vbInformation + vbOKOnly, "Format Macro")

End Sub