Save as Macro named content of cell

[josullivan601632]

Hi can anyone help me write a macro. I'm all good with excel and basic recorded macros, but VBA and the more advanced stuff I always fail at!!! I am working on a spreadsheet with one main tab called "InternalQuote" which feeds information into various other tabs and I want to be able to click on an image (which I will assign to the macro) whilst in the "InternalQuote" tab to;

Save the whole workbook as a new xlsm. document named the contents of "K9" and "K7" of another tab called "Quotation"

I also want to click on another image (which I will assign to the macro) to save the tab titled "Quotation" as a PDF, named also as the contents of "K9" and "K7" which also will open once created for the user to check.

Is this possible? And would anyone be able to spare some precious time and send me something to perhaps (cheekily) copy and paste?

 

[JW00]

Try these, with the pdf you may have adjust your sheet to fit.

Sub SaveAsPDF()
Dim strTempFile As String
Dim strData As String
Dim spath As String

    spath = Environ("USERPROFILE") & "\Desktop\"
    strData = Sheets("Quotation").Range("K9").Value & Sheets("Quotation").Range("K7").Value & ".pdf"
    strTempFile = spath & strData

    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, OpenAfterPublish:=True

End Sub

Sub SaveWorkbook()
Dim strTempFile As String
Dim strData As String
Dim spath As String

    spath = Environ("USERPROFILE") & "\Desktop\"
    strData = Sheets("Quotation").Range("K9").Value & Sheets("Quotation").Range("K7").Value & ".xlsm"
    strTempFile = spath & strData
    
    ActiveWorkbook.SaveCopyAs Filename:=strTempFile
    
End Sub

 

[josullivan601632]

Try these, with the pdf you may have adjust your sheet to fit.

Sub SaveAsPDF()
Dim strTempFile As String
Dim strData As String
Dim spath As String

    spath = Environ("USERPROFILE") & "\Desktop\"
    strData = Sheets("Quotation").Range("K9").Value & Sheets("Quotation").Range("K7").Value & ".pdf"
    strTempFile = spath & strData

    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, OpenAfterPublish:=True

End Sub

Sub SaveWorkbook()
Dim strTempFile As String
Dim strData As String
Dim spath As String

    spath = Environ("USERPROFILE") & "\Desktop\"
    strData = Sheets("Quotation").Range("K9").Value & Sheets("Quotation").Range("K7").Value & ".xlsm"
    strTempFile = spath & strData
   
    ActiveWorkbook.SaveCopyAs Filename:=strTempFile
   
End Sub

Hi Thank you so much for your time. both return a invalid 400 code error, I run a tab array on the doc which works find so I think the doc is ok with running macros, is there any thing else I can check to correct the 400 error?

 

[JW00]

Sorry I know nothing about 400 errors, check my spelling matches your workbook, if that's ok then I would try copying your "Quotation" sheet to a new workbook & run the code in there, other than that check out 400 Error Solutions, both codes run fine on my pc.

 

[josullivan601632]

I copied both sheets to a new book and the codes worked a treat. I even changed the filepath and put a couple of spaces in the file names!!! Thank you for helping me do this (well actually you did it), but I have def picked up a teeny bit more knowledge, the code looks so simple when it is written for you.

One thing, when the excel doc is saved, it works so lovely that I am going in to check it is saved, I am sure there is a pop up that can be added via macro something like "file saved and the filename", can this be added to the macro you already wrote?

Have a great day

 

[JW00]

Thanks, I'm glad it's working, I presume you added the spaces to the strData as part of the file name, if so you can just add "MsgBox strData, , "File Saved" underneath the current code & you will get a message box titled "File Saved" with your filename below, I hope that helps & all the best with your project.

 

[josullivan601632]

That is a fabulous added extra. All works perfect. Can't thank you enough

 

[JW00]

Thanks for your kind words, sometimes the finishing touch makes all difference, good luck with it all.

 

[josullivan601632]

Hi sorry to bother you again, all still working great (not broken it yet) but I have tried to add an additional macro to the workbook, practically the same as before but this 2nd PDF button prints the brochure instead of the quote, the brochure is another tab. It is returning a 400 error which I know you said you didn't know much about, but I wonder if this is do with my writing of the code. Please would you mind checking?

THE ONE YOU WROTE (without the no.1):

Sub SaveAsPDF1()
Dim strTempFile As String
Dim strData As String
Dim spath As String

spath = Environ("USERPROFILE") & "\Newman Refrigeration Ltd\Newman Refrigeration Ltd - General\Brian\Cost Book\"
strData = Sheets("Quotation").Range("K9").Value & " - " & Value & Sheets("Quotation").Range("K1").Value & ".pdf"
strTempFile = spath & strData
MsgBox strData, , "PDF has been saved with the following filename:"

Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, OpenAfterPublish:=True

End Sub

THE ONE I HAVE ADDED, SO JUST COPIED AND PASTED THE ORIGINAL CODE AND ADDED THE NO. 2 (IN RED) SO THEY ARE DIFFERENT BECAUSE IT SEEMED TO WANT ME TO DO THAT!

Sub SaveAsPDF2()
Dim strTempFile As String
Dim strData As String
Dim spath As String

spath = Environ("USERPROFILE") & "\Newman Refrigeration Ltd\Newman Refrigeration Ltd - General\Brian\Cost Book\"
strData = Sheets("Quotation").Range("K9").Value & " - " & Value & Sheets("Quotation").Range("K1").Value & ".pdf"
strTempFile = spath & strData
MsgBox strData, , "PDF has been saved with the following filename:"

Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, OpenAfterPublish:=True

End Sub


It ends with the PDF saved pop up then returns the 400 error, so think it must be something I must be doing but do want to learn what I am doing wrong! I promise I wont keep bothering you!!!!



Any thoughts?

 

[JW00]

Hi, it looks like you have an error in both,

strData = Sheets("Quotation").Range("K9").Value & " - " & Value & Sheets("Quotation").Range("K1").Value & ".pdf"

remove that part & give it a try.