Call up a Macro within another Macro

[John]

I am trying to run a simple sort macro (this is called SORT) which I have
recorded, within another macro. I have typed the word SORT in a line within
my main macro thinking that this would activate the sort macro at that
point, but I'm getting the following error message at the SORT part, Compile
Error: expected variable or procedure not Module

My full macro is as follows

Public Sub PostToAccounts()

Sort

Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With



strSheetName = Worksheets(ActiveSheet.Name).Range("D4")

Sheets.Add
ActiveSheet.Name = strSheetName & "_Acc"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Input")

lngPosY = 9 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "DOCUMENT_TYPE"
Range("D1") = "DOCUMENT_YEAR"
Range("E1") = "DOCUMENT_PERIOD"
Range("F1") = "DOCUMENT_DATE"
Range("G1") = "Nominal"
Range("H1") = "Subaccount"
Range("I1") = "Level3"
Range("J1") = "Document_Value"
Range("K1") = "Description"
Range("L1") = "JR_DATE"
Range("M1") = "JR_YEAR"
Range("N1") = "JR_PERIOD"

Range("A2") = "1" ' The document number
Range("B2") = "1"
Range("C2") = "CLAD"
Range("D2") = Worksheets("Input").Range("AC3")
Range("E2") = Worksheets("Input").Range("AC4")
Range("F2") = Worksheets("Input").Range("AC5")
Range("N2") = Worksheets("Input").Range("AC2")
Range("L2") = Worksheets("Input").Range("AC5")
Range("M2") = Worksheets("Input").Range("AC1")

lngOutY = 3

Do While Len(Worksheets("Input").Range("aa" & lngPosY)) > 0
Range("A" & lngOutY) = "1" ' The document number
Range("B" & lngOutY) = lngOutY - 1
Range("C" & lngOutY) = "CLAD"
Range("D" & lngOutY) = Worksheets("Input").Range("AC3")
Range("E" & lngOutY) = Worksheets("Input").Range("AC4")
Range("F" & lngOutY) = Worksheets("Input").Range("AC5")
Range("G" & lngOutY) = Worksheets("Input").Range("aa" & lngPosY)
Range("H" & lngOutY) = Worksheets("Input").Range("ab" & lngPosY)
Range("I" & lngOutY) = Worksheets("Input").Range("ac" & lngPosY)
If Worksheets("Input").Range("AF" & lngPosY) > 0 Then
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AF" &
lngPosY), 2)
Else
Range("J" & lngOutY) = Round(Worksheets("Input").Range("AG" &
lngPosY) * -1, 2)
End If
Range("K" & lngOutY) = Worksheets("Input").Range("AD" & lngPosY)

lngPosY = lngPosY + 1
lngOutY = lngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
Columns("D:E").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
Selection.NumberFormat = "M/D/YYYY"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "M/D/YYYY"
Columns("M:N").Select
Selection.NumberFormat = "0"
Cells.Select
Cells.EntireColumn.AutoFit

For i = Range("B700").End(xlUp).Row To 2 Step -1
If Left(Range("B" & i).Value, 3) <> "1" Then
If Range("J" & i).Value = 0 Then
Range("J" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=(B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b700").End(xlUp).Row)
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Range("A2").Select

MsgBox "Sheet Converted - Now Post To Accounts"


End Sub

 

[Nikos Yannacopoulos]

John,

I believe that's because Sort is a reserved word in Excel VB, actually a
method; change the macro name (and the call) to something different, and it
will be called correctly.

HTH,
Nikos

 

[Rob van Gelder]

Possibly you are confusing a module with a macro.

A macro is a section of code starting with the word Sub or Function (it can
also be Public Sub or Public Function)

macros live in modules

You see modules in your Project Explorer window.

 

[Don Guillett]

Not necessarily true but you are correct in that it is best to name SortJoe
or something else. Try this

Sub checksort()
MsgBox "hi"
sort
MsgBox "low"
End Sub

Sub sort()
MsgBox "Will do"
End Sub

 

[Don Guillett]

It might be that your sort macro is PRIVATE.
You should also try to remove as many selections as possible. Example.

with Rows("1:1")
.Font.Bold = True
.Font.ColorIndex = 6
.Interior.ColorIndex = 49
End With

Columns("A:B").NumberFormat = "0"
Columns("C:C").NumberFormat = "@"

 

[Myrna Larson]

What is the name of the module containing the Sort macro? Sounds to me as
though it's Sort. As others have mentioned, Sort is a method of the range
object, so it's best to use a different name for your procedures, and it's
also best to not use the same name for both a module and a procedure.