Help to edit code to add save as option, please!

[GARY_DANIEL]

Hi all,

I am looking for to pick your brains over an issue I am having with regards to adding in an Excel button to 'save as'.

Basically, I have...

• Created the button
• Edited the code so that I am only saving a selection of cells (A1:I95) and in PDF format.

This saves my file to desktop, as PDF and under the title 'Quote'.

The issue I am having is that I when I save the file, unless I manually rename the file it will overwrite any previos saves without a promp as it always saves as 'Quote'.

Is there a way that I can either save it as 'QuoteV1', V2, V3 and so on
or
(Preferably) When I click the Save button I have created, I am promted to change file name to one of my choice, whilst still having the setting as 'save selction only' and as a PDF file?

Sorry if this is a relatively easy fix, I am very new to this! I have tried to play around with a few codes myself and have searched far and wide for a solution.

Your help is greatly appreciated!

Kind regards,
Gary.

 

[Caleeco]

Hello Gary,

Welcome to the forum. The problem you've described is not the simplest problem to solve, however, it's certainly far from the hardest to solve

Could you please post the exisiting code you have using CODE tags, and we can have a look at adding the functionality you require.

Many Thanks
Caleeco

 

[GARY_DANIEL]

Hi Caleeco

I used the record macro feature to get this far, there is also an issue with the selection of those cells, as it is a protected sheet the highlight that I put over the cells to select them during the record stays after I have saved.
Code is as follows...

T

Sub DETAILEDQUOTESAVEALLPDF()
'
' DETAILEDQUOTESAVEALLPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
ActiveWindow.SmallScroll Down:=-24
ActiveWindow.View = xlNormalView
Range("A1:I95").Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\brunigeg\Desktop\DETAILED QUOTE.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
ActiveWindow.SmallScroll Down:=-9
Range("K69").Select
ActiveWindow.SmallScroll Down:=-66
End Sub
Sub PRINTDETAILEDQUOTE()
'
' PRINTDETAILEDQUOTE Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
ActiveWindow.SmallScroll Down:=-3
Range("A1:I95").Select
Selection.PrintOut Copies:=1, Collate:=True
End Sub



Thank you!

 

[GARY_DANIEL]

Bottom Sub is for a print button I also have on the page. Only just noticed its on there!

 

[Caleeco]

Hello Gary,

Thank you for your code. I have done some house-keeping on it (hope you don't mind). By removing the SELECT methods, you will remove the problem you had with the highlight.

Sub DETAILEDQUOTESAVEALLPDF()
 '
 ' DETAILEDQUOTESAVEALLPDF Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+J
 
 ActiveSheet.Range("A1:I95").ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
 "C:\Users\brunigeg\Desktop\DETAILED QUOTE.pdf", Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
 True
  
 End Sub
 
 Sub PRINTDETAILEDQUOTE()
 '
 ' PRINTDETAILEDQUOTE Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+P
 '
 ActiveSheet.Range("A1:I95").PrintOut Copies:=1, Collate:=True
 End Sub

So if i understand your request, what you need is: Everytime you hit the save macro, a PDF will save with a file name "DETAILED QUOTE VX"... where "X" is 1,2,3,4.... and so on?

Is that correct?
Regards
Caleeco

 

[GARY_DANIEL]

Yes, that would be perfect!

I shall be using it on a different sheet too, with a shorter range of cell selection, could you also point out what I'd need to change to make it relevent?

Much appreciated!

Gary.

 

[Caleeco]

Yes, that would be perfect!

I shall be using it on a different sheet too, with a shorter range of cell selection, could you also point out what I'd need to change to make it relevent?

Much appreciated!

Gary.

Hello Gary,

Ok thanks for the info. I have a couple of follow up questions based on your respose:
1. The other sheet you wish to use it on, will it be saving to the same file location and file name layout? i.e "DETAILED QUOTE VX"
2. Is this other sheet in the same workbook?
3. Is the full filename always going to be in the format "DETAILED QUOTE VX.pdf"? i.e. it doesnt get changed once processsed.

Let me know
Many Thanks
Caleeco

 

[Caleeco]

Hello,

Had a bit of a play around with some code. This is more of a general approach. It will take the current selection, prompt you for a file name and then save as.

Sub SaveSelection()
Dim data As Range


Set data = Selection
   Dim FName As Variant
    FName = Application.GetSaveAsFilename( _
        InitialFileName:="DETAILED QUOTE.pdf", _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")
    If FName <> False Then
        data.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
    End If
End Sub

Let me know if this is something you can use, or if you need something more specific eg. export a different range for each worksheet.

Many Thanks
Caleeco

 

[GARY_DANIEL]

Hello,

The other sheet will be saved as 'QUOTE VX.pdf'
It is in the same work book.
The file name may be changed as several people will have access and will be saving to their own desktops.

Does that make sense? I shall try the code above and feedback on the outcome.

Regards,
Gary.

 

[GARY_DANIEL]

Also, the selected cells will always remain the same page to page. Can this be set within the code?