View Single Post
 
Old 09-27-2020, 09:35 AM
NoSparks NoSparks is offline Windows 10 Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
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