Run-time error '9': Subscript out of range on Copy/Paste VBA?

[dougmarkham]

Hi Folks,

I have this copy/paste VBA which copies a range from one workbook into another workbook.

Public Sub CopyPaste()
Workbooks("GenericGRNReport(Excel2010).xlsm").Sheets("GRN_Data").Range("$E$3:$F$5000").Copy Destination:=Workbooks("113LastNightStockManagementReportMACRO1.xltm").Sheets("Stock Management Lookup").Range("$A$3:$B$5000")
End Sub

The Sheet names and Workbook names are all correct. Is there something wrong with this code that would cause a Run-time error 9?

Kind regards,

Doug.

 

[PatOBrien198]

.xltm

Should that be: .xlsm?

 

[dougmarkham]

.xltm

Should that be: .xlsm?

Hi Pat,

It's an macro-enabled excel Template file. When I double click on the file, it opens the file name plus an extra 1 on the end of it.
So the question might be: will that opened macro-enabled excel Template file still be an *.xltm file, or does it open as an *.xlsm file?

Kind regards,

Doug.

 

[dougmarkham]

Hi Pat,

It's an macro-enabled excel Template file. When I double click on the file, it opens the file name plus an extra 1 on the end of it.
So the question might be: will that opened macro-enabled excel Template file still be an *.xltm file, or does it open as an *.xlsm file?

Kind regards,

Doug.

So I've tried replacing the *.xltm file extension with *.xlsm, but the error message persists.
Any ideas?

 

[Norie]

Doug

When you double click on a template workbook then a new workbook based on the template is created and it will normally have a name consisting of the template name, without the extension, followed by a number.