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]

That line of code doesn't compile. It should be:

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

 

[Jimmy P]

OK. Fixed the".xlsx: goof. Still having the same error occur and a new blank workbook "Bookxx" is created but never saved. I get UserName from the following Public Sub.
Thought this made it available to all procedures in the module?

Public Sub UserName()

Dim UserName As String
Sheets("Calc").Select
ActiveSheet.Range("R1").Select

UserName = Application.InputBox("Enter your Name in the box below and Click OK. " _
, Default:=" ", Title:="User Name")
Sheets("Calc").Range("T1") = UserName
End Sub

 

[Jimmy P]

UpDate - Boy does this log you off fast...

Everything works OK now until I get to the "Workbooks.Open ...." line and then I get an Application-defined error. I can see the created workbook with the correct name, including date/time, and I can see that the source sheet has stepped into the copy function, but it hits Workbooks.Open it and chokes. I did a ? curdir in the Immediate window and the correct directory is current so it must be looking there, but doesn't recognize the file for some reason. I set a Watch at the offending line and it reports <expression not="" defined="" in="" context=""> underValue "Expression not defined in context"</expression><expression not="" defined="" in="" context=""><expression not="" defined="" in="" context=""> , and Type = Empty.

The weird thing is the code is from a recorded Macro I did and all I added was the Format(Now... part.

Sub NewWB()
'' NewWB Macro
' Workbooks.Add
ChDir "C:\Users\Jim 2\Desktop\Sample Results"
ActiveWorkbook.SaveAs Filename:= _

"C:\Users\Jim 2\Desktop\Sample Results\CopiedResults" & Format(Now(), " mmm-dd-yyyy hh.mm") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Cells.Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\Jim 2\Desktop\Sample Results\CopiedResults"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub</expression></expression>

 

[Andrew Poulsom]

You haven't specified the entire file name,

 

[Jimmy P]

Thanks Andrew. That's what I thought. Problem is the file name changes every time it is created and there may be hundreds of files in the directory to reopen for the paste. File name created is "CopiedResults 9/08/2014 12:26" for one I just ran. How do I pick the last file created n the directory to use in the statement to open and paste into?

 

[Andrew Poulsom]

Can't you store the file name in a variable?

    Dim FileName As String
    ChDir "C:\Users\Jim 2\Desktop\Sample Results"
    FileName = "CopiedResults" & Format(Now(), " mmm-dd-yyyy hh.mm") & ".xlsx"
    ActiveWorkbook.SaveAs FileName:= _
        "C:\Users\Jim 2\Desktop\Sample Results\" & FileName, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Cells.Select
    Selection.Copy
    Workbooks.Open FileName:="C:\Users\Jim 2\Desktop\Sample Results\CopiedResults\" & FileName
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close

But why are you closing then reopening it?

 

[Jimmy P]

Andrew, Your fix works great. It now creates the file OK.

About your ? on closing the file: If I don't, it chokes at the Cell.Select line where I want to copy from the source file and then go back to the new file and paste it. If I leave in the close it closes the source file and stop, but the new file has the data OK. The sequence I need is:

Create new file
Copy source to it
Close new file
Stay in source file

I'm sure it's simple, but as a VBA newbie I am lost.

 

[Andrew Poulsom]

What's name of the Worksheet that you want to copy? Is it in the Workbook that contains your VBA code?

 

[Jimmy P]

Yes. Workbook name for source file is Test1.xlsx Workbook name to be created is C:\Users\Jim 2\Desktop\Sample Result + date/time .xlsx