Solved: Add a named workbook

[Pete634]

Hi,

Can anyone give me a simple code to add a named workbook e.g. "TEST" with only one worksheet "A", but in doing so I do the code MUST NOT
1. save the "TEST" workbook
2. alter the number of sheets available for future new workbooks because of this code (because this will be used in a multi-user environment, and you know how stroppy people can get if suddenly when they click create new workbook they get only 1 sheet instead of 3, or 10 or 16!)

The "TEST" workbook is purely to be used as a dump to temporarily store data, which I don't want to keep in my main workbook.

Please help !!

[mdmackillop]

Hi Pete,
I don't think you can change the name without saving it. You can use Test as a variable to refer to the workbook within code.

Sub NewBook()
Dim Test As Workbook
ThisWorkbook.Sheets.Add
ActiveSheet.Move
Set Test = ActiveWorkbook
End Sub

[Pete634]

Thanks Malcolm.

I didn't think I could do it and you have confirmed!

Never mind, I can workaround.

[johnske]

As it's a temporary workbook, there's no reason you can't save it with a new name and then get rid of it at the end of the procedure. e.g.

Option Explicit

Sub TestIt()
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
.SaveAs ThisWorkbook.Path & "\TEST.xls"

'run your code here

'now kill this workbook
.Saved = True
.ChangeFileAccess Mode:=xlReadOnly
Kill .FullName
.Close False
End With
End Sub