Closing one workbook with a varying name

C

Craig

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.
 
K

Kletcho

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.
 
T

Tom Ogilvy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top