CODEWITHSUNDEEP
vbaexcel
Shah A
Shah A

Reputation: 13

application.run (macro,Arg1,Arg2)

I would like to kindly request for assistance with the following issue:

I am trying to run a Macro that runs multiple macros but it is based on 2 Strings as follows:

Sub ExportDatatoCountriesSheets()

    Dim shtnme As String
    Dim country As String

    ' United States
    shtnme = "US"
    country = "United States"
    ' ClearLatestData
    Application.Run "ClearLatestData"
    ' FilterExportDataByCountry
    Application.Run "FilterExportDataByCountry"

    ' Japan
    shtnme = "JP"
    country = "Japan"
    ' ClearLatestData
    Application.Run "ClearLatestData"
    ' FilterExportDataByCountry
    Application.Run "FilterExportDataByCountry"

End Sub

'==============================================================

Sub ClearLatestData()

    Dim shtnme As String
    Dim country As String
    Sheets(CStr(shtnme)).Activate
    Columns("A:Z").Select
    Selection.Clear

End Sub

'==============================================================

Sub FilterExportDataByCountry
Sheets("WEEKLY DATA").Select
    ActiveSheet.Range("$A$1:$G$240").AutoFilter Field:=3,    Criteria1:=CStr(country)
    Columns("A:G").Select
    Selection.Copy
    Sheets(CStr(shtnme)).Activate
    Range("A1").Select
    ActiveSheet.Paste

However, I keep getting errors. I essentially would like to set the ExportDatatoCountriesSheets Macro to repeat while I enter the code and run the others by Application.Run

I've searched and closest solution is to run the Macro with arguments but when I define the arguments the macro does not run properly and error occurs.

Application.Run (ClearLatestData, shtnme = "US", country = "United States")

Please advise if possible.

Upvotes: 1

Views: 1200

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

In order to call the "ClearLatestData" MACRO (Sub) you need to add the 2 parameters to the receiveing Sub. So the syntax should be:

Sub ClearLatestData(shtnme As String, country As String)

and inside the Sub ClearLatestData you will remove the 2 lines of declaring the String variables:

Dim shtnme As String
Dim country As String

To call Sub ClearLatestData with the shtnme and country, use:

Application.Run "ClearLatestData", shtnme, country

Or, a "cleaner" shorter version:

ClearLatestData shtnme, country

Code

Sub ExportDatatoCountriesSheets()

Dim shtnme As String
Dim country As String

' United States
shtnme = "US"
country = "United States"
' ClearLatestData
ClearLatestData shtnme, country ' <-- call the MACRO    
' FilterExportDataByCountry
Application.Run "FilterExportDataByCountry"

' Japan
shtnme = "JP"
country = "Japan"

' ClearLatestData
ClearLatestData shtnme, country ' <-- call the MACRO    
' FilterExportDataByCountry
Application.Run "FilterExportDataByCountry"

End Sub

' ================================================================

Sub ClearLatestData(shtnme As String, country As String)

    Sheets(CStr(shtnme)).Activate
    Columns("A:Z").Select
    Selection.Clear

End Sub

Note: it's recommended to stay away from Activate, Select and Selection, and use qualified Worksheets, Ranges and other objects instead.

For instance, instead of the 3 lines below:

Sheets(CStr(shtnme)).Activate
Columns("A:Z").Select
Selection.Clear

You can use a single line (and it will be faster since you don't use Activate and select):

Sheets(shtnme).Columns("A:Z").Clear

Upvotes: 1

Related Questions