Macro to save sheet as value in cell

[BMcHale]

Hi all,

I'm trying to write a macro that saves a sheet as a unique name, varying upon the value of a specific field.

For example a name I would be after would be "JR Export MAR".

This is what I have so far, however it is't working.

Sub Macro1()
'
' Macro1 Macro
'


'
    Dim Filename1 As String
    Filename1 = Workbooks("JR CSV Creator.xlsm").Sheets("Input").Range("A1").Value
    
    Workbooks.Add
    ChDir "M:\Palanning\Transport\Trucks"
    ActiveWorkbook.SaveAs Filename:= _
        "M:\Palanning\Transport\Trucks\JR Export & Filename1 & .xlsx", FileFormat:=xlsx _
        , CreateBackup:=False

End Sub

Any Ideas?

Thanks,
Billy

 

[Tim_Excel_]

Hi,
please use CODE tags when pasting code.

[COLOR=#333333]Sub SaveCustomName()[/COLOR]

[COLOR=#333333]Dim Filename1 As String[/COLOR]
[COLOR=#333333]Filename1 = Workbooks("JR CSV Creator.xlsm").Sheets("Input").Range("A1").Value[/COLOR]

[COLOR=#333333]Workbooks.Add[/COLOR]
[COLOR=#333333]ChDir = "M:\Palanning\Transport\Trucks\"[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:= ChDir & "[/COLOR][COLOR=#333333]JR Export " & Filename1 & ".xlsx", FileFormat:=xlsx _[/COLOR]
[COLOR=#333333], CreateBackup:=False[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

Does this work for you?

 

[BMcHale]

Hi,
Yes sorry about that, only realised I hadn't once I'd posted.

There's an error being thrown up highlighting ChDir saying "Argument not optional"

Thanks,
Billy

 

[Tim_Excel_]

I wasn't familiar with ChDir, thought it was just a regular variable. Change ChDir to sPath and you'll be fine

 

[Fluff]

try

Sub Macro1()
'
' Macro1 Macro
'


'
    Dim Filename1 As String
    Filename1 = Workbooks("JR CSV Creator.xlsm").Sheets("Input").Range("A1").Value
    
    Workbooks.Add
    ChDrive "M:"
    ChDir "M:\Palanning\Transport\Trucks"
    ActiveWorkbook.SaveAs fileName:= _
        "M:\Palanning\Transport\Trucks\JR Export " & Filename1 & ".xlsx", FileFormat:=51
End Sub

 

[BMcHale]

Hi Guys,

Thanks, this has worked perfectly.

Billy

 

[Fluff]

Glad we could help & thanks for the feedback