Create new workbook, then use that WB name as a variable

Question

I'm trying to create a new workbook to save worksheets to. I create the new workbook, prompt for name. This all works, my problem is then I need to create a variable with that new name, so I can more easily jump back and forth when copying worksheets over to it.

Sub SaveInfo()
'
' SaveInfo Macro
'
Dim NWB As Workbook
Dim TWB As Workbook
Dim Fname As String

Dim MyTargetFile As Variant
Dim UserInput As Variant
Dim WBName As String

Workbooks.Add 
MyOpenedFile = ActiveWorkbook.Name

'Here get a file name from user
UserInput = Application.InputBox(prompt:="Enter File name to save")

If UserInput <> "" Then
'Save the opened excel file by renaming it , here UserInput is used  as new file name.
Workbooks(MyOpenedFile).SaveAs Filename:="G:\Clearwater\Archive Data\" & UserInput
End If
'Here I try to get that name of the new workbook
    WBName = ThisWorkbook.Name
    'Here I try to declare that workbook as a variable
    Set NWB = Workbooks("WBName")

    Set TWB = Workbooks("Historical Data Creation Tool ERIK New.xlsm")

This is where I will call that "New Workbook" NWB over and over again

TWB.Activate
Sheets("CW_AmortEarn").Select
Sheets("CW_AmortEarn").Copy Before:=NWB.Sheets(1)

TWB.Activate
Sheets("CW_FMV_FY").Select
Sheets("CW_FMV_FY").Copy Before:=NWB.Sheets(1)

I'm stuck. Perhaps there is a better way then what I'm trying to do here. Any ideas would be helpful.

Normally you don't need to `Activate` or `Select`. See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for more detail. But more importantly, [`Workbooks.Add`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) returns a `Workbook` object. So you could do `Set NWB = Workbooks.Add`. — BigBen, Oct 08 '19 at 01:02
Yep, Know that. Not the problem. Just use that when I'm working through code to get things working, then I clean up. I Can't figure out how to declare the variable NWB as the new workbook I created. I'll go through and clean up my code later. — Erik Otterholt, Oct 08 '19 at 01:05
BigBen, I used workbooks add at the top of my code, now I need to get the name from the new workbook where I prompt the user for input so that I can call that same workbook over and over again. — Erik Otterholt, Oct 08 '19 at 01:08
Both I and @TimWilliams have already pointed out that you don't need to do that. See the posted answer. — BigBen, Oct 08 '19 at 01:11
I do need to do that. The script will not work. I can't call the new workbook back to paste worksheets to with out it. — Erik Otterholt, Oct 08 '19 at 01:12
Please give the posted answer a try. You are claiming that it doesn't work, but it does not seem like you have actually given the solution a try. Thanks! — BigBen, Oct 08 '19 at 01:14
Ben, I really appreciate the help, but I don't think your understanding me. I have to name the new workbook because 1.) I have to anyhow, and 2.) if I run this script over and over, the new workbook name always changes from book1 to book2 book3 ect. So, I can't get the sheets into the new workbook every time I run the code when the new workbook name is different. So, if I can figure out how to take the name and make that a variable, then I have what I need. That's what I'm stuck on. — Erik Otterholt, Oct 08 '19 at 01:17
Please [edit] your question with what you have tried, thanks. — BigBen, Oct 08 '19 at 01:17
It *does* work - people including me use it regularly with no problems. As BigBen suggests, it would help us help you if you could update your question to show exactly what you tried and what happened when you ran that code. — Tim Williams, Oct 08 '19 at 03:01

score 1 · Answer 1 · answered Oct 08 '19 at 01:06

Edit: @BigBen already pointed this out in a comment...

Workbooks.Add returns a reference to the added workbook, so capture that and use it:

Dim wb As Workbook, UserInput 

Set wb = Workbooks.Add()

UserInput = Application.InputBox(prompt:="Enter File name to save")

If UserInput <> "" Then
    wb.SaveAs Filename:="G:\Clearwater\Archive Data\" & UserInput
End If

'wb is still a reference to the newly-added workbook
Debug.Print wb.Name

Create new workbook, then use that WB name as a variable

1 Answers1