Save as unique file name but then I need Activate that file name

[s_woodbury]

I have a script that makes the user choice which xls file to use. The script saves the file with a unique name by using todays date.

That works, it's when I try to use Windows(xxxxxxxxxxx).Activate that I have problems.

Here's how I save the file.

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\swoodbury\Documents\Old HR LO List-" _
& Format$(Date, "MMDDYYYY") _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

What can I use to activate that window? I tried:

Windows("New HR LO List-" & Format$(Date, "MMDDYYY") & ".xls").Activate

Any advice is appreciated! Thanks - Scott

 

[s_woodbury]

Correction: All code is "Old HR LO List-"
but still have errors

 

[Jerry Sullivan]

Hi Scott,

After Workbook.SaveAs, the new workbook should be the ActiveWorkbook (with a new name). Is that not the case for you?

You should be able to refer to the new workbook in one of these ways....

 ActiveWorkbook.SaveAs Filename:= _
     "C:\Users\swoodbury\Documents\Old HR LO List-" _
     & Format$(Date, "MMDDYYYY") _
     , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'--reference the activeworkbook...
 Msgbox "New Name is: " & ActiveWorkbook.Name

'---or assign the new workbook object to a variable
 Dim wkbNew as Workbook
 Set wkbNew = ActiveWorkbook
 Msgbox "New Name is: " & wkbNew.Name

 

[s_woodbury]

Thanks Jerry! Perfect!