Save Active Sheet as new workbook, in specific folder, with a filename from a cell value

[jambo9685]

Hi.

I'm pretty new to VBA's and Macros, so apologies if this is a daft request.


I have a workbook where I would like to save the active worksheet as a new workbook, in a specific C: location and use the value in a cell as the filename.

I have done this in the past, but it was exporting as a PDF, whereas now I want to save this as an Excel Workbook. The code below is for the working PDF export, can this be tweaked to change the file type or does it need to be brand-new code?


Private Sub CommandButton2_Click()
Dim strFileName As String, strC3 As String, strWorksheet As String
'Set Variable Values
strC3 = Range("E39").Value
strWorksheet = ActiveSheet.Name
strFileName = strC3 & " " & strWorksheet

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\LinicModules\" & Range("E50").Value, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub


Thank you in advance

[Mumps1]

Try:

Please Login or Register  to view this content.

[jambo9685]

Hi Mumps1

That was worked perfectly. Thank you so much for your help. Hugely appreciated.

[Mumps1]

My pleasure.

[jambo9685]

Hi Mumps1. Sorry to bother you. I have just saved the document as an .xltm template (to stop anybody from overwriting this master copy) and now when I use this code, I am getting the pop-up message - Save 'Book1' with references to unsaved documents? - and the new XLSX document isn't being saved.

Thanks again

[Mumps1]

The following steps worked for me:
-After saving the file as an .xltm template, close it.
-Click 'File' …. 'New' ….. 'New from existing'
-Locate the template and open it.
-Run the macro.

[jambo9685]

It's working again now, thank you. But I have noticed that the file size is surprisingly big.

The Macro Enabled WorkBook is 19MB, when I first used the saveas Macro, because it is only saving the active worksheet, the file size was 64KB. Today, the same saveas Worksheet is 18MB. It doesn't have the hidden tabs or the same embedded Macros, so I can't understand why the file size is so huge. Any thoughts?

Thank you

[Mumps1]

To be honest, I don't know why that would happen. I tried the macro on a dummy file several times and each time it produced a file that was smaller than the macro-enabled workbook as it should. If I could see the 18MB file, I might get a better idea. I don't think that the Forum allows attachments that big. You could try and see what happens. Alternately, you could upload a copy of the file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.