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

 

[Michael M]

your missing a Bracket at the end of Sheets...

ActiveWorkbook.SaveAs Filename:="1. " & sheets("Control_Page"[color=red])[/color].Range("B12")&" Monthly P&L Review.xlsx"

 

[Tigrou25]

Thanks but it comes with a runtime error 2147352565.

Here is the complete 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") & " Monthly P&L Review.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

 

[Michael M]

Remove the underscore from

ChDir _"S:

 

[Tigrou25]

Thanks. The macro runs but it ends with a run-time error '9'. Subscript out of range

 

[Michael M]

So, one of sheets names is either incorrect or doesn't exist.
Check for leading trailling spaces or maybe that underscore in Control_Page

 

[Tigrou25]

I have the following error message

 

[Michael M]

Ok, so either
1. you dn't have access to the database on S;\ drive
2. the Drive you are accessing doesn't exist
3. The data source may be locked or protected

But I think you will have to resolve the issue at the Dbase or file location as I don't think it's a VBA issue

 

[Tigrou25]

That's weird because I can save the file with a standard name but I get this message when I try to save it with the data from the Control_Page tab

 

[Michael M]

OK, try adding value to the string
AND
What is actually in B12 on the control page ?

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") [color=red].value[/color] & " Monthly P&L Review.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close