|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range.
Sub main()
Dim i As Integer Dim wrk As Workbook Dim wsh As Worksheet, wrk_Name, wsh_Name, prmpt As String prmpt = "Enter Workbook name:" wrk_Name = Application.InputBox(prmpt) prmpt = "Enter Worksheet name:" wsh_Name = Application.InputBox(prmpt) Set wrk = Workbooks(wrk_Name) 'This line gives error '.... end sub |
#2
|
|||
|
|||
First - create the new workbook with Workbooks.add
This workbook automatically becomes the active workbook. Second - name the workbook using SaveAs giving it a location, name and file format. If the file format is not stated the default as set in Excel's options is used. Put your cursor on SaveAs and hit F1 to see built in help. also see here Then name the sheet, which will be the first sheet in the new workbook Code:
'Set wrk = Workbooks(wrk_Name) 'This line gives error Set wrk = Workbooks.Add wrk.SaveAs "D:\" & wrk_Name, FileFormat:=56 ' 56=.xls ActiveSheet.Name = wsh_Name '.... Also, when declaring more than one variable on the same line you need to use as for each one otherwise they are being declared as variant. |
Tags |
worked fine previously. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Runtime error '9': Subscript out of Range | KingoftheKassel | Excel Programming | 1 | 06-13-2016 08:00 PM |
Error: "Changes made were lost...reconnect with server", when switching "from" field | randhurrle | Outlook | 2 | 02-25-2015 06:51 PM |
Loop through folder of workbooks and copy range to other workbook | Snvlsfoal | Excel Programming | 3 | 07-29-2011 05:55 AM |
Subscript out of range error | KHTAY | Excel | 6 | 03-28-2009 11:18 PM |
Mysterious "Subscript out of range" error | rnstewart | Excel | 4 | 12-29-2005 01:04 PM |