z00h Wrote:
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("Shee t1")

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("Shee t1")

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


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519668