Activeworkbook Macro

[ianto2842]

Hello. I have a macro in a workbook that opens a separate workbook and copies in some data. I then want to run the macro that is in the newly opened workbook NOT the original one. The code runs fine up to the point where it needs to run the macro. I'm assuming the wording needs to be different and I can't just use 'Call', since the macro isnt saved in the same workbook as the overarching macro. Suggestions? Thanks

Dim yearmonthday As String
Dim foldername As String
Dim fname As String

yearmonthday = Range("D3") & Range("C3") & Range("B3")
foldername = Range("B23")
fname = Range("B22")

'open 'Working File' workbook'
Workbooks.Open "\\group\Shared\Public\Folder\" & foldername & "\" & fname & ".xlsx" _

'Select data
    Set Rng = Range("A1:AP" & Cells(Rows.Count, "A").End(xlUp).Row)
    Rng.Select
    Rng.Copy
   
'Open Converter, paste in data and run macro
Workbooks.Open "P:\Folder\Folder\Converter.xlsm"
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
       
ActiveWorkbook.Call Run

 

[Joe4]

If I understand what you are asking correctly, you shouldn't use the "ActiveWorkbook" in front of the call to the macro.
Just try:

Call Run

As long as the workbook containing the procedure named "Run" is open, and you are on the workbook you want to apply it to (the one you just opened), it should work on that workbook, by default.

 

[ianto2842]

If I understand what you are asking correctly, you shouldn't use the "ActiveWorkbook" in front of the call to the macro.
Just try:

Call Run

As long as the workbook containing the procedure named "Run" is open, and you are on the workbook you want to apply it to (the one you just opened), it should work on that workbook, by default.

Hey, thanks for this but I get an "Invalid procedure call or argument" debug error. I'm assuming that's because the Run macro isn't saved in the original workbook? I assumed the same as you though originally, that as the active workbook had already been set, I could just write the VBA as if it were the normal workbook, but that doesn't seem to be the case...

 

[Joe4]

Hey, thanks for this but I get an "Invalid procedure call or argument" debug error. I'm assuming that's because the Run macro isn't saved in the original workbook? I asusmed th same as you though originally, that as the active workbook had already been set, I could just write the VBA as if it were the normal workbook, but that doesnt seem to be the case...

Is your procedure really named "Run"? If so, change it immediately.
You should NEVER used reserved works like "Run" as the name of procedures, functions, or variables.
It can cause errors and unexpected results.

Can you post the code of the "Run" procedure?
Is it marked as a "Private" procedure?

 

[Fluff]

Try using run instead

Run "'" & ActiveWorkbook.Name & "'!run"

But as Joe has said, you should change the name of the sub

 

[ianto2842]

Is your procedure really named "Run"? If so, change it immediately.
You should NEVER used reserved works like "Run" as the name of procedures, functions, or variables.
It can cause errors and unexpected results.

Can you post the code of the "Run" procedure?
Is it marked as a "Private" procedure?

Yea I probably should do that...
And here you go. It works fine when used on it's own. It might not be the cleanest way of doing it, but I'm still learning. It essentially copies the data into a different tab as values, copies it out into a new workbook, saves and closes then clears out the initial data and closes.

Sub Run()
'
' Run Macro
'

    Application.ScreenUpdating = False
    Sheets("Working3").Visible = True
    Sheets("GRV").Visible = True
    Sheets("Working3").Select
    
    LastRow = Range("AT2").Value
    Set Rng = Range("A1:AP" & LastRow)
    Rng.Select
    Rng.Copy
    
    Sheets("GRV").Select
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Sheets("GRV").Select
    Sheets("GRV").Copy
    
    ThisFile = Range("AR3").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile

    Columns("A:AP").EntireColumn.AutoFit
    
    Columns("AQ:AR").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    
    ActiveWorkbook.Save
    ActiveWindow.Close
    
    Sheets("GRV").Select
    Columns("A:AP").Select
    Selection.ClearContents
    Range("A1").Select

    Sheets("Data Drop").Select
    Range("A2:AP2000").Select
    Selection.ClearContents
    Range("A1").Select

    
    Sheets("Working3").Visible = False
    Sheets("GRV").Visible = False
    
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    
    
'
End Sub

 

[ianto2842]

Try using run instead

Run "'" & ActiveWorkbook.Name & "'!run"

But as Joe has said, you should change the name of the sub

This has sorted it! Thanks
I have renamed the sub also, thnaks for the roasting everyone... :P

 

[Fluff]

Glad we could help & thanks for the feedback.