"Call" to run other macros

[bobkap]

Is there a limit to how many "Call" statements you can make in a macro? I have a "Master" macro that holds the following 4 statements:

Call HST_create_sheet
Call HST_move_columns
Call HST_Mo_Bill_Format
Call Format2

When I run the Master macro only the first two of these run. If I do a Debug/Run To Cursor, I can then manually step through the last two, but they will not all run on their own.

Any help would be greatly appreciated.

 

[L. Howard]

Maybe HST_Mo_Bill_Format has a routine something like...

If This or That then
Do this thing

Else

Exit Sub

End If

Where the Do this thing does not occur or happen and would then exit the Sub Master() instead of reverting back to Master to call Format2.

Howard

 

[bobkap]

Thanks very much. I kind of tried that already and it did not work. Specifically, I added Call HST_Mo_Bill_Format and Call Format2 to the end of Call HST_move_columns but it still didn't work.

 

[Fluff]

Could you post the code for the second macro?

 

[bobkap]

Thanks for asking. Here it is.

 Sheets("Summary").ActivateColumns("A:G").Select
    Selection.Columns.AutoFit
    ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("A2:A761" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Summary").Sort
        .SetRange Range("A1:G761")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("F:F").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("A2").Select

 

[Fluff]

Is that the HST_move_columns macro?

 

[bobkap]

No it is not. Here's the code for that macro. Please note that i stil have some debugging to do in some of the beginning code.

Sub HST_move_columns()'
' HST_move_columns Macro
'
'
Sheets("Sheet1").Activate
finalrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
finalrow2 = Sheets("Promo").Cells(Rows.Count, 1).End(xlUp).Row
finalcol = Cells(1, Columns.Count).End(xlToLeft).Column
colnum = 1
rownum = 1
colnum2 = 1


'Find header that says Promo Code and then make header in Summary tab "Customer" as we will be converting the promo code to the customer's name
'And filling that in under this header
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "Promo Code" Then
pcode = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Cells(1, colnum2) = "Customer"
Application.CutCopyMode = False
Exit For
End If
colnum = colnum + 1
Next counter


'Convert and replace promo code with full customer name
counter = 1
colnum = 1
rownum = 2
For counter = counter To finalrow
pcode = Sheets("Promo").Cells(rownum, colnum)
If pcode = "" Then
Sheets("Sheet1").Rows(rownum).Delete
Shift = xlShiftUp
Else
Custname = Application.WorksheetFunction.VLookup(pcode, Sheets("Promo").Range(Sheets("Promo").Cells(2, 1), Sheets("Promo").Cells(finalrow2, 2)), 2, False)
On Error Resume Next
Sheets("Summary").Cells(rownum, colnum) = Custname
End If
rownum = rownum + 1
Next counter


counter = 1
colnum = 1
colnum2 = colnum2 + 1
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "First Name" Then
Regdate = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Application.CutCopyMode = False
End If
colnum = colnum + 1
Next counter


counter = 1
colnum = 1
colnum2 = colnum2 + 1
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "Last Name" Then
Regdate = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Application.CutCopyMode = False
End If
colnum = colnum + 1
Next counter


counter = 1
colnum = 1
colnum2 = colnum2 + 1
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "Department:" Then
Regdate = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Application.CutCopyMode = False
End If
colnum = colnum + 1
Next counter


counter = 1
colnum = 1
colnum2 = colnum2 + 1
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "Course" Then
Regdate = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Application.CutCopyMode = False
End If
colnum = colnum + 1
Next counter


counter = 1
colnum = 1
colnum2 = colnum2 + 1
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "Course Date" Then
Regdate = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Application.CutCopyMode = False
End If
colnum = colnum + 1
Next counter


counter = 1
colnum = 1
colnum2 = colnum2 + 1
For counter = counter To finalcol
If Sheets("Sheet1").Cells(1, colnum) = "Promo Code" Then
Regdate = Sheets("Sheet1").Range(Cells(1, colnum), Cells(finalrow, colnum)).Copy
Sheets("Summary").Cells(1, colnum2).PasteSpecial Paste:=xlPasteValues
Sheets("Summary").Application.CutCopyMode = False
End If
colnum = colnum + 1
Next counter


'Enter "Amount Paid" header in farthest right empty top row cell
Sheets("Summary").Activate
rownum2 = 2
finalcol2 = Sheets("Summary").Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("Summary").Cells(1, finalcol2 + 1) = "Amount Paid"
finalcol2 = Sheets("Summary").Cells(1, Columns.Count).End(xlToLeft).Column


finalcol = Cells(1, Columns.Count).End(xlToLeft).Column
counter = 1
colnumpc = 1
rownumpc = 2
For counter = counter To finalcol
If Sheets("Summary").Cells(1, colnumpc) = "Promo Code" Then
PC = Cells(rownumpc, colnumpc)
Exit For
End If
colnumpc = colnumpc + 1
Next counter


finalcol = Cells(1, Columns.Count).End(xlToLeft).Column
counter = 1
colnumco2 = 1
rownumco2 = 2
For counter = counter To finalcol
If Sheets("Summary").Cells(1, colnumco2) = "Course" Then
Co2 = Cells(rownumco2, colnumco2)
Exit For
End If
colnumco2 = colnumco2 + 1
Next counter


finalcol = Cells(1, Columns.Count).End(xlToLeft).Column
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
counter = 1
colnum = 1
rownum = 2


'Determine amount paid per student using just-moved data in Summary tab


counter2 = 1
For counter2 = counter2 To finalrow
For counter = counter To finalcol
If Sheets("Summary").Cells(1, colnum) = "Amount Paid" Then 'And Not IsEmpty(Cells(rownumpc, colnumpc)) Then
amountpaid = Sheets("Promo").Application.WorksheetFunction.VLookup(PC, Sheets("Promo").Range("table2"), WorksheetFunction.Match(Co2, Sheets("Promo").Range("bk"), 0), False)
Sheets("Summary").Cells(rownum, colnum) = amountpaid
Exit For
End If
colnum = colnum + 1
Next counter


rownumpc = rownumpc + 1
rownumco2 = rownumco2 + 1
PC = Sheets("Summary").Cells(rownumpc, colnumpc)
Co2 = Sheets("Summary").Cells(rownumco2, colnumco2)
rownum = rownum + 1
Next counter2


'
End Sub
[code/]

 

[Fluff]

Remove this line

On Error Resume Next

& see what happens.

 

[bobkap]

Thanks! That makes perfect sense. I'll try it!