Closing one workbook with a varying name
 

Hi,

I'm trying to close one specific workbook that I've opened with the
Workbooks.Add command. This causes the workbook to come up with a name
that changes (book1, book2, etc.). After the rest of the code has
finished, I want to close this one workbook, leaving all other active
workbooks open. I've been trying to work with Workbooks.Close but that
keeps closing all workbooks. So the next thing I tried went like this.

Sub OpenandClose()
Dim wB as String
wB = "WorkBook"
Workbooks(wB).Add
'rest of code
Workbooks(wB).close
End Sub

But that's not working either - getting error 9, subscript out of range
on Workbooks(wB).Add

Any thoughts? Thanks in advance for any help.

Closing one workbook with a varying name
 

dim wb as workbook

set wb = Workbooks.Add
wb.close



if needed you can do a wb.save to give the new workbook a name.

Closing one workbook with a varying name
 

Sub OpenandClose()
Dim wB as Workbook
Dim wbName as String
wbName = "Myworkbook.xls"
set wB = Workbooks.Add
wB.SaveAs thisworkbook.path & "\" & wbName

msgbox wB.Name
msgbox workbooks(wbName).Name

wb.close Savechanges:=True
End Sub

--
Regards,
Tom Ogilvy

"Craig" wrote:

Hi,

I'm trying to close one specific workbook that I've opened with the
Workbooks.Add command. This causes the workbook to come up with a name
that changes (book1, book2, etc.). After the rest of the code has
finished, I want to close this one workbook, leaving all other active
workbooks open. I've been trying to work with Workbooks.Close but that
keeps closing all workbooks. So the next thing I tried went like this.

Sub OpenandClose()
Dim wB as String
wB = "WorkBook"
Workbooks(wB).Add
'rest of code
Workbooks(wB).close
End Sub

But that's not working either - getting error 9, subscript out of range
on Workbooks(wB).Add

Any thoughts? Thanks in advance for any help.

Closing one workbook with a varying name
 

Thanks to both of you, both methods worked.