Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-26-2020, 08:21 PM
Syed Shabbir Syed Shabbir is offline Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Windows 10 Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Office 2007
Novice
Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range.
 
Join Date: Sep 2020
Posts: 1
Syed Shabbir is on a distinguished road
Question 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
Reply With Quote
  #2  
Old 09-27-2020, 09:35 AM
NoSparks NoSparks is offline Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Windows 10 Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Reply With Quote
Reply

Tags
worked fine previously.

Thread Tools
Display Modes


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
Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Subscript out of range error KHTAY Excel 6 03-28-2009 11:18 PM
Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Mysterious "Subscript out of range" error rnstewart Excel 4 12-29-2005 01:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:00 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft