Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
You don't specify the path in lines like this:
XLSBook1.Worksheets("Sheet1").Copy _ after:=Workbooks("C:\TEST_1.xls").Worksheets("Shee t1") 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 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
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" wrote: You don't specify the path in lines like this: XLSBook1.Worksheets("Sheet1").Copy _ after:=Workbooks("C:\TEST_1.xls").Worksheets("Shee t1") 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 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
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 wrote: 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" wrote: You don't specify the path in lines like this: XLSBook1.Worksheets("Sheet1").Copy _ after:=Workbooks("C:\TEST_1.xls").Worksheets("Shee t1") 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 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range
Thanks alot, will test and revert if any more questions arise
Greets "Dave Peterson" wrote: 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 wrote: 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" wrote: You don't specify the path in lines like this: XLSBook1.Worksheets("Sheet1").Copy _ after:=Workbooks("C:\TEST_1.xls").Worksheets("Shee t1") 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 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of Range | Excel Discussion (Misc queries) | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of range | Excel Programming | |||
Subscript out of range | Excel Programming |