Macro to save concatenate names

[Tigrou25]

Hi all,

I have created a macro to extract few tabs from a spreadsheet and save a new spreadsheet.

I would like to save the new file using the month in the tab "Control_Page" (in cell B12).

I have tried the code below

ActiveWorkbook.SaveAs Filename:= _
"1. " & sheets("Control_Page".Range("B12")&" Monthly P&L Review.xlsx"

which doesn't work.

The idea would be to have the following file name

1. Oct 18 Monthly P&L Review.xlsx

with Oct18 being cell B12 in the tab "Control_Page"

Thanks for your help

 

[Tigrou25]

Here's the tab Control_Page and cell B12.

This is the right name

I have added the value to the string but now I have a syntax error.

Here is the code

 ChDir "S:\Construction\National - Head Office\A Profit and Loss\A01 FY Budgets\08. FY19 P&L\1.Monthly P&L Review"ActiveWorkbook.SaveAs Filename:= _
"1. " & Sheets("Control_Page").Range("B12") .value & " Monthly P&L Review.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Michael M]

Have a look at the syntax for "Control_Page" in your code .....then look at the sheet Tab syntax Control_page
There is a difference !

 

[Tigrou25]

No more luck with this one, still syntax error

        ChDir "S:\Construction\National - Head Office\A Profit and Loss\A01 FY Budgets\08. FY19 P&L\1.Monthly P&L Review"ActiveWorkbook.SaveAs Filename:= _"1. " & Sheets("Control_page").Range("B12") .value & " Monthly P&L Review.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Michael M]

Ok, you stated previously that you could save it this way if you used a standard name ??
But not when you use Rnge (B12)......So, there must be an issue with B12 !
Try setting as a variable like

Set mydate =  Sheets("Control_page").Range("B12") .value

then use

ChDir "S:\Construction\National - Head Office\A Profit and Loss\A01 FY Budgets\08. FY19 P&L\1.Monthly P&L Review"
ActiveWorkbook.SaveAs Filename:= _"1. " & mydate & " Monthly P&L Review.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

If you then test it line by line using F8, when you hover the mouse over mydate in the save line it should tell you if it is correct or not !

Otherwise upload the file to dropbox and we'll take a look at it.

 

[Tigrou25]

Like this ?

Set mydate =  Sheets("Control_page").Range("B12") .value        ChDir "S:\Construction\National - Head Office\A Profit and Loss\A01 FY Budgets\08. FY19 P&L\1.Monthly P&L Review"
ActiveWorkbook.SaveAs Filename:= _"1. " & mydate & " Monthly P&L Review.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Michael M]

What happens if you just use this and see if it does as required

Sub MM1()
Set mydate = Sheets("Control_page").Range("B12")
ActiveWorkbook.SaveAs Filename:="1. " & mydate & " Monthly P&L Review.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

 

[Tigrou25]

This code is saving the file with the correct name including the month in documents.

I have added the location to save the file but it still saving in documents

ChDir "S:\Construction\National - Head Office\A Profit and Loss\A01 FY Budgets\08. FY19 P&L\1.Monthly P&L Review"Set mydate = Sheets("Control_page").Range("B12")
ActiveWorkbook.SaveAs Filename:="1. " & mydate & " Monthly P&L Review.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Michael M]

Ok, so now what does this do ??

Dim fpath As String
fpath = "S:\Construction\National - Head Office\A Profit and Loss\A01 FY Budgets\08. FY19 P&L\1.Monthly P&L Review\"
Set mydate = Sheets("Control_page").Range("B12")
ActiveWorkbook.SaveAs Filename:=fpath & "1. " & mydate & " Monthly P&L Review.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Tigrou25]

More over, when I include the code in my original macro, I still get the same error message

 

[Michael M]

I'm afraid without seeing the workbook, I can't really help any further.
If you are getting to the line you highlighted in yellow, then it isn't the SaveAs that's causing the problem.

Is "Control_Page" protected ???