Workbooks.add (?RefName?)

[kartune85]

I've written out the following code but when I try and reference it
later on in the code, it keeps telling me 'Object Required'.

Sub AddNew()
Dim NewBook As Workbook

Set NewBook = Workbooks.Add
With NewBook
..Title = "Purchase Order"
..Subject = mycount
End With

End Sub

Sub MoveSheet()
ThisWorkbook.Worksheets(1).Move Before:=NewBook.Worksheets(1)
End Sub

When I type in 'NewBook.' it pops up with the menu but when the code
runs it tells me that I require an object.

I was given this code the other day:

Dim NewWB As WorkBook
Set NewWB=Workbook.Add (..../ Workbook.Open (....

I'm not sure what was meant by the code after 'Add'. Does this have
some involvement in referencing the NewWB?

Kartune85

 

[Nick Hodge]

Your variable scope is at procedure level. If you wish to reference it in
another one, move it outside any procedure and it will have module level
scope

remember to explicitly empty it though

Set NewBook=Nothing

when you're done with it

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[kartune85]

I tried putting the 'Dim NewBook As Workbook' outside all of the
procedures but it says only comments can be placed outside Sub
Procedures. I also tried putting it in 'Sub Auto_Open()' but it still
doesn't recognize the reference.

If I put 'NewBook = Workbooks.Add' elsewhere, it won't execute at the
point I want it to execute.

So how do I "move it outside any procedure"? (I assume you're refering
to 'Dim NewBook As Workbook')

 

[kartune85]

I used:

Public NewBook As Workbook

...right at the the top of the page before any procedures. And In the
close procedure I put:

Sub CloseBook()
Set NewBook = Nothing
Application.DisplayAlerts = False
ThisWorkbook.Close
Application.DisplayAlerts = True
End Sub

It all works sweet now. Someone can now open multiple documents without
having conflict errors.

Thanks for your help.

Kartune85

 

[Nick Hodge]

Great

thanks for the feedback

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)