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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
So, is there a sheet names "Latest" in the other workbook ?
If not, that's your problem !!
 

sukpmp

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

sukpmp

New Member
Joined
Dec 24, 2018
Messages
6

ADVERTISEMENT

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!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

sukpmp

New Member
Joined
Dec 24, 2018
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

sukpmp

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,142
Messages
5,527,064
Members
409,742
Latest member
setam

This Week's Hot Topics

Top