Error Running a Macro in another Workbook

sukpmp

New Member
Joined
Dec 24, 2018
Messages
6
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
So, is there a sheet names "Latest" in the other workbook ?
If not, that's your problem !!
 
Upvote 0
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.
 
Upvote 0
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...

Code:
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
 
Upvote 0
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...

Code:
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!
 
Upvote 0
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"?
 
Last edited:
Upvote 0
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.
 
Upvote 0
The below runs fine for me when running the
Code:
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.

Code:
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
 
Last edited:
Upvote 0
The below runs fine for me when running the
Code:
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.

Code:
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top