When recording

  • Thread starter Thread starter Copacetic
  • Start date Start date
C

Copacetic

I open or "add" a new workbook.

It records fine, but the call to "book7" is hard coded, and in the
future, the code will not be seeing "book7", so how do I call the newly
created workbook by other than its creation name? (I suppose I could
simply name it).
 
Or use:

ActiveWorkbook

But that only works so long at the new workbook is active. The best thing
is to name the workbook as soon as it is created.

myPath = ThisWorkbook.Path '<<<Change as applicable

Set newBk = Workbooks.Add
'Rename and save the file simultaneously.
newBk.SaveAs = myPath & "\Somename.xls" 'Or xlsx or xlsm, etc.

Workbooks("Somename.xls").Close
 
I'd use a variable to represent that newly added workbook.

Dim NewWkbk as workbook
set newwkbk = workbooks.add

Then I could use refer to that workbook whenever I wanted.

newwkbk.worksheets(1).range("a1").value = "hi"

newwkbk.close savechanges:=false
 
Hmmmm... Colonel Clinc say "Vedy interesting..."

I should be able to use this replacement form in a few other
circumstances too. I need to become familiar with this little ghostly
fellow.

Thanks.
 
Back
Top