Error Running a Macro in another Workbook

[sukpmp]

Hi, I am running a macro in another workbook. This macro runs OK within it's own workbook but when I try to run it from another workbook I get a run-time error 9 subscript of out range.

If I opt for debug it seems to stop on the line in bold! Any idea why?

Sub CopyLatest()
'Updateby20140717
Dim szTodayDate As String
szTodayDate = Format(Date, "dd-mmm-yyyy")
On Error GoTo MakeSheet
Sheets(szTodayDate).Activate
Exit Sub
MakeSheet:
Sheets("Latest").Select
Sheets("Latest").Copy After:=Sheets("Mint")
ActiveSheet.Name = szTodayDate

Sheets("Latest").Select

End Sub

 

[Michael M]

So, is there a sheet names "Latest" in the other workbook ?
If not, that's your problem !!

 

[sukpmp]

So, is there a sheet names "Latest" in the other workbook ?
If not, that's your problem !!

Thanks for the response.
The tab "Latest" exists in the same workbook as the CopyLatest macro. I want the macro to run in that workbook not the one I'm initiating it from.

 

[MARK858]

Unless you specify the workbook then the code as written will run on whatever workbook is active at the time.
Ads you state "The tab "Latest" exists in the same workbook as the CopyLatest macro" then possibly...

Sub CopyLatest()
    'Updateby20140717
    Dim szTodayDate As String
    szTodayDate = Format(Date, "dd-mmm-yyyy")
    On Error GoTo MakeSheet
    Sheets(szTodayDate).Activate
    Exit Sub
MakeSheet:
    ThisWorkbook.Sheets("Latest").Select
    ThisWorkbook.Sheets("Latest").Copy After:=ThisWorkbook.Sheets("Mint")
    ActiveSheet.Name = szTodayDate

    ThisWorkbook.Sheets("Latest").Select

End Sub

 

[sukpmp]

Unless you specify the workbook then the code as written will run on whatever workbook is active at the time.
Ads you state "The tab "Latest" exists in the same workbook as the CopyLatest macro" then possibly...

Sub CopyLatest()
    'Updateby20140717
    Dim szTodayDate As String
    szTodayDate = Format(Date, "dd-mmm-yyyy")
    On Error GoTo MakeSheet
    Sheets(szTodayDate).Activate
    Exit Sub
MakeSheet:
    ThisWorkbook.Sheets("Latest").Select
    ThisWorkbook.Sheets("Latest").Copy After:=ThisWorkbook.Sheets("Mint")
    ActiveSheet.Name = szTodayDate

    ThisWorkbook.Sheets("Latest").Select

End Sub

it doesn't seem to like the ThisWorkbook at the start of those references!

 

[MARK858]

What exactly do you mean by "it doesn't seem to like"? The code compiles fine for me. did you copy/paste the code or type/amend it?

And is the sheet "Mint" in the same workbook as "Latest"?

 

[sukpmp]

What exactly do you mean by "it doesn't seem to like"? The code compiles fine for me. did you copy/paste the code or type/amend it?

And is the sheet "Mint" in the same workbook as "Latest"?

yes, I've copied and pasted your code into a new marco called copylatest2 - this is saved in the same workbook as the tabs latest and mint. If I run the macro from within their workbook it works OK. However, I am trying to run it from another workbook using the following code:

Sub runit()
Application.Run "'xxx v9 Latest.xlsm'!CopyLatest2"
End Sub

I get a '1004' runtime error.

 

[MARK858]

The below runs fine for me when running the

Sub runit()
Application.Run "'xxx v9 Latest.xlsm'!CopyLatest2"
End Sub

code from a 2nd workbook, check your spelling. In particular check your spacing in between words is correct and you have no leading or trailing spaces.

Sub CopyLatest2()
Dim szTodayDate As String
'    'Updateby20140717
'    Dim szTodayDate As String
   szTodayDate = Format(Date, "dd-mmm-yyyy")
'    On Error GoTo MakeSheet
'    Sheets(szTodayDate).Activate
'    Exit Sub
'MakeSheet:
    ThisWorkbook.Sheets("Latest").Activate
    ThisWorkbook.Sheets("Latest").Copy After:=ThisWorkbook.Sheets("Mint")
    ActiveSheet.Name = szTodayDate

    ThisWorkbook.Sheets("Latest").Activate

End Sub

 

[sukpmp]

The below runs fine for me when running the

Sub runit()
Application.Run "'xxx v9 Latest.xlsm'!CopyLatest2"
End Sub

code from a 2nd workbook, check your spelling. In particular check your spacing in between words is correct and you have no leading or trailing spaces.

Sub CopyLatest2()
Dim szTodayDate As String
'    'Updateby20140717
'    Dim szTodayDate As String
   szTodayDate = Format(Date, "dd-mmm-yyyy")
'    On Error GoTo MakeSheet
'    Sheets(szTodayDate).Activate
'    Exit Sub
'MakeSheet:
    ThisWorkbook.Sheets("Latest").Activate
    ThisWorkbook.Sheets("Latest").Copy After:=ThisWorkbook.Sheets("Mint")
    ActiveSheet.Name = szTodayDate

    ThisWorkbook.Sheets("Latest").Activate

End Sub

All working now, thanks so much.

I'd missed that you'd commented out some of the lines further in the macro. I presume they were causing the issue.

 

[MARK858]

All working now, thanks so much.

I'd missed that you'd commented out some of the lines further in the macro. I presume they were causing the issue.

No, I commented them out because they were irrelevant to the question you were asking and couldn't be bothered to create another sheet based on szTodayDate.
Putting the lines back should have no effect on the error you were getting.

Anyway happy you have it sorted.

Sub CopyLatest2()
    Dim szTodayDate As String
    szTodayDate = Format(Date, "dd-mmm-yyyy")
    On Error GoTo MakeSheet
    Sheets(szTodayDate).Activate
    Exit Sub
MakeSheet:
    On Error GoTo 0
    ThisWorkbook.Sheets("Latest").Activate
    ThisWorkbook.Sheets("Latest").Copy After:=ThisWorkbook.Sheets("Mint")
    ActiveSheet.Name = szTodayDate

    ThisWorkbook.Sheets("Latest").Activate

End Sub