Saving file as returns error "424": object neccessary

[confused in Frankfurt]

Hi there,
I have a macro to create a worksheet and it is not saving the file returning the error 424.

In my code I define the folder and the file name.

Dim Folder As String
Folder = "A:\Controlling\2014\Reporting\CARS\Template\CARS\06_Jun\"

Dim RSM As String

Selection.Offset(0, 2).Select 'cell in file where RSM name
RSM = Selection

Dim timestamp As String
timestamp = Format(Date, "yyyymmdd")

Dim NBName As String
NBName = "CARS_" & RSM & timestamp & ".xlsm"

ActiveWorkbooks.SaveAs Filename:=Folder & NBName _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

when I scroll over the defined names in the last code it shows the following:


-- removed inline image ---


-- removed inline image ---

I am not sure why the file is not saving and which object is missing.

Regards
Sarah

 

[Norie]

It should be ActiveWorkbook not ActiveWorkbooks.

 

[confused in Frankfurt]

Thanks but now I have the error
runtime error '1004':
the methode 'SaveAs' has failed for the Object'_Workbook'

 

[NewOrderFac33]

Should

RSM = Selection

be

RSM = Selection.value

Pete

 

[confused in Frankfurt]

still the same error

 

[confused in Frankfurt]

Should

RSM = Selection

be

RSM = Selection.value

Pete

Still showing the same error

 

[NewOrderFac33]

This works - I have created a new string for use as your filename.

    Dim Folder As String
    Folder = "A:\Controlling\2014\Reporting\CARS\Template\CARS\06_Jun\"
    
    Dim RSM As String
    
    Selection.Offset(0, 2).Select 'cell in file where RSM name
    RSM = Selection.formula
    
    Dim timestamp As String
    timestamp = Format(Date, "yyyymmdd")
    
    Dim NBName As String
    NBName = "CARS_" & RSM & timestamp & ".xlsm"
    
    '###Extra string created here
    Dim MyFileName As String
    MyFileName = Folder & NBName
         
    '###Extra string used as filename
    ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[confused in Frankfurt]

This works - I have created a new string for use as your filename.

    Dim Folder As String
    Folder = "A:\Controlling\2014\Reporting\CARS\Template\CARS\06_Jun\"
    
    Dim RSM As String
    
    Selection.Offset(0, 2).Select 'cell in file where RSM name
    RSM = Selection.formula
    
    Dim timestamp As String
    timestamp = Format(Date, "yyyymmdd")
    
    Dim NBName As String
    NBName = "CARS_" & RSM & timestamp & ".xlsm"
    
    '###Extra string created here
    Dim MyFileName As String
    MyFileName = Folder & NBName
         
    '###Extra string used as filename
    ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

now I must be going crazy as I still have the same error.

Maybe one note, the definitions are in different parts of the macro the Timestamp and folder are at the beginning outside of a loop, the other definitions are within the loop

 

[Norie]

What loop?

 

[NewOrderFac33]

Well, wherever your lines of code appear, your original issue was that as well as Norie's suggestion to change ActiveWorkbooks to ActiveWorkbook, you need to create your filename by concatenating dates, cell contents and string values into one string variable, which you then use as a paramater in your ActiveWorkbook.Save command.
With things like this, I also ways find it handy to add MsgBoxes to my code, so you can see the values of variables as you go along. Try adding

MsgBox (MyFilename)

immediately before your ActiveWorkbook.Save command, so you can see which bits of your filename haven't been added in correctly. Without all the correct bits, it may amount to an invalid filename.

Of course, it always helps to post the relevant code in its entirety..!