Save WorkSheet as per Cell VALUE (keep original open, and do not open new)

[TTUK]

Hello all,

I am trying to achieve the following within VBA.

1. Save specific worksheet (IIR_temp) to a location shown in the cell in A1 on that page, which is a reference from another sheet =IIR_data!5 which looks like (\\server\report\test.xls)
2. Then have the sheet just be saved, and not to override the current spreadsheet which is open


At present this is what I have:

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveSheet.SaveAs Range("A1").Value, xlOpenXMLWorkbookMacroEnabled


I just don't know how to get the formula to save the certain sheet in the specified location shown in A1, and allow me to return as I was in the original spreadsheet.

Hope someone can help me out on this.

Thank you!

 

[Fluff]

If you want an xls file try

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("A1").Value, 56
ActiveWorkbook.Close

 

[TTUK]

Hello Fluff,

I have tried your code.
It create a new worksheet, however, it does not close automatically.
Also it there I can get this new workbook to save as per the name in cell B1?

Thank you

 

[Fluff]

When it created the new workbook, was that new workbook the active workbook?
To use cell B1 as the name just change A1 to B1

 

[TTUK]

When it created the new workbook, was that new workbook the active workbook?
To use cell B1 as the name just change A1 to B1

Hello Fluff,

Yes the new workbook was the active workbook.
I need the new workbook saved in the specific file location of A1
With the specific file name of B1.

Regards, Charlie

 

[Fluff]

In that case use

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("A1").Value & Range("B1").value, 56
ActiveWorkbook.Close

You'll need to make sure that the value of A1 ends with a \ or this won't work

 

[TTUK]

Hi Fluff,

Sadly the spreadsheet is not changing it's file name to suite nor is it saving it in the specific location.
I have ensured that a \ is added.

Thanks,

 

[Fluff]

Do you get any error messages?

 

[TTUK]

Hello Fluff,

No I do not get any error message.
It creates the new file and has it as the active sheet, however the file name of the document isn't at the top and it is just showing 'Book 11' for example.
And it isn't saving the file nor closing afterwards either.

Thank you,

 

[Fluff]

What are the values in A1 & B1 on the new workbook?