Subscript out of range

[z00h]

Can you please help?

I've created following macro

------------------------------------------------
Public Function TEST()
Dim XLSApp As Excel.Application
Dim XLSBook1, XLSBook2 As Workbook

Set XLSBook1 = ActiveWorkbook

Set XLSApp = New Excel.Application
XLSApp.Visible = True
Set XLSBook2 = XLSApp.Workbooks.Add

XLSBook2.SaveAs "C:\TEST_1.xls"

XLSApp.Quit

XLSBook1.Worksheets("Sheet1").Copy _
after:=Workbooks("C:\TEST_1.xls").Worksheets("Sheet1")

End Function
------------------------------------------------

In general I wanted to copy Sheet1 form ActiveWorkbook to the Workbook
created and saved under "C:\TEST_1.xls" by this macro.
The only and biggest problem I get is with the

XLSBook1.Worksheets("Sheet1").Copy _
after:=Workbooks("C:\TEST_1.xls").Worksheets("Sheet1")

It always returns "Run Time Error '9' Subscript out of range".
What might be the reason for it?

TIA

 

[davesexcel]

Can you please help?

I've created following macro

------------------------------------------------
Public Function TEST()
Dim XLSApp As Excel.Application
Dim XLSBook1, XLSBook2 As Workbook

Set XLSBook1 = ActiveWorkbook

Set XLSApp = New Excel.Application
XLSApp.Visible = True
Set XLSBook2 = XLSApp.Workbooks.Add

XLSBook2.SaveAs "C:\TEST_1.xls"

XLSApp.Quit

XLSBook1.Worksheets("Sheet1").Copy _
after:=Workbooks("C:\TEST_1.xls").Worksheets("Sheet1")

End Function
------------------------------------------------

In general I wanted to copy Sheet1 form ActiveWorkbook to the Workbook
created and saved under "C:\TEST_1.xls" by this macro.
The only and biggest problem I get is with the

XLSBook1.Worksheets("Sheet1").Copy _
after:=Workbooks("C:\TEST_1.xls").Worksheets("Sheet1")

It always returns "Run Time Error '9' Subscript out of range".
What might be the reason for it?

TIA

A couple of things come to mind
Are the workbooks open that you want to paste into,
is your spelling correct of your worksheets

I have found that this message comes up when Excel can't find
something,
maybe you need to activate Sheet 1 A1 and then paste

 

[Dave Peterson]

You don't specify the path in lines like this:

XLSBook1.Worksheets("Sheet1").Copy _
after:=Workbooks("C:\TEST_1.xls").Worksheets("Sheet1")

it's just:

XLSBook1.Worksheets("Sheet1").Copy _
after:=Workbooks("TEST_1.xls").Worksheets("Sheet1")

But since you set a reference to xlsbook2, you could use that:

XLSBook1.Worksheets("Sheet1").Copy _
after:=xlsbook2.Worksheets("Sheet1")

========
But if this code is being run from Excel, then you don't need that second
instance of excel started.

I would use something like:

Option Explicit
Public Function TEST()

Dim XLSBook1 As Workbook
Dim XLSBook2 As Workbook

Set XLSBook1 = ActiveWorkbook
Set XLSBook2 = Workbooks.Add

XLSBook2.SaveAs "C:\TEST_1.xls"

XLSBook1.Worksheets("Sheet1").Copy _
after:=XLSBook2.Worksheets("Sheet1")

End Function

=========
Or maybe something more like this -- when you copy a worksheet and don't specify
a location, xl creates a new workbook:

Option Explicit
Public Function TEST()

Dim XLSBook1 As Workbook
Dim XLSBook2 As Workbook

Set XLSBook1 = ActiveWorkbook

XLSBook1.Worksheets("Sheet1").Copy 'to a new workbook

Set XLSBook2 = ActiveWorkbook

XLSBook2.SaveAs "C:\TEST_1.xls"

End Function

======

One more thing, this line:

Dim XLSBook1, XLSBook2 As Workbook

Declares xlsbook1 as a variant--not a workbook.

 

[z00h]

Thanks for a tip

Running second instance of Excel is necessary as all this has to be run in
background.

The idea is following:
- user edits the workbook1,
- entered data is being pasted to the special template sheet in workbook1
- then this sheet is being copied in the backgroud from workbook 1 to
workbook2. Workbook2 has to be created within this macro as everytime it is
has to be new one.

I am quite fresh to Visual Basic so maybe there is other method of copying
sheets between two instances?

Brgds

 

[Dave Peterson]

I'm not sure what you mean by run in the background. If you turn off screen
updating, it'll look like nothing is happening. And it looks to me like this
code would run pretty quickly.

But if you really want to run it in a second instance, you'll have to open your
source workbook in that instance, do the copy, save, and close both workbooks,
then close that instance of excel.

 

[z00h]

Thanks alot, will test and revert if any more questions arise

Greets