Application Defined error on creating new workbook

[Jimmy P]

I have the following sub to create a new workbook, close it, copy a worksheet in the active workbook, open the new workbook, and then paste it in. It worked in Excel 2007, but I've switched to 2010 and now I get an Application Defined or Object defined error (1004). It stops at the third line (ActiveWorkbook.SaveAs......) with the error. It does create a new workbook, but it's a Bookn file name (as in Book1 etc.), but never saves it. UserName is the output of an InputBox (Dim UserName As String, ). This works great and puts the string in a cell. I've tried using the Worksheet Range where UserName is stored in the file name and still get the same error.



Sub CreateCopy()

Workbooks.Add
ChDir "C:\Users\Jim 2\Desktop\Sample Results"
ActiveWorkbook.SaveAs Filename:="C:\Users\Jim 2\Desktop\Sample_Results\"UserName & Format(Now(), "mm-dd-yyyy hh:mm").xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\Jim 2\Desktop\Sample_Results\UserName.xlsx"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close

Application.DisplayAlerts = True
End Sub

 

[Andrew Poulsom]

What's the name of the Worksheet that you want to copy?

 

[Jimmy P]

The worksheet is "Samples."

The File name should be: UserName & Sample Results & Date/Time & .xlsx
I added another variable for UserName to grab it from a cell in the workbook. I got this the work:

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Jim 2\Desktop\Sample Results\Sample Results " & format(Now(), "mm-dd-yyyy hh:mm) & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

But when I add:

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Jim 2\Desktop\Sample Results\UserName & Sample Results " & format(Now(), "mm-dd-yyyy hh:mm) & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

It just puts UserName at the head of the filename and not he cell contents..

 

[Norie]

I see no reason why you need to save/close the workbook you are creating.

If you are having problems referring to it in later code try creating a reference to it when you create it.

For example,

Dim wbNew As Workbook

   Set wbNew = Workbooks.Add

now when you need to refer to the newly created workbook in the rest of the code you can use wbNew.

 

[Jimmy P]

Norie, Great tip. I'll file this one for reference. I've got the issue nearly solved except the correct syntax for using a variable as the first part of the file name. Look a couple of posts up. If you have any suggestions I'm appreciative.

 

[Andrew Poulsom]

Try:

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Jim 2\Desktop\Sample Results\" & UserName & Sample Results " & format(Now(), "mm-dd-yyyy hh:mm) & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Jimmy P]

Andrew,

I tried your code and it would still only put the UserName variable name in the file name instead of the actual stored User Name. Got syntax error. I changed the code to the below and it worked with all the variable being used in the file name. Go figure!

ActiveWorkbook.SaveAs FileName:= _
"C:\Users\Jim 2\Desktop\Sample Results\Sample Results " & UserName & "-" & SSID & Format(Now(), " mmm-dd-yyyy hh-mm AM/PM") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I can live with this.

Many thanks for your patience and persistence in helping solve all the issues I was having.

Jim