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]

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

Hi Gary,

For the situation you described, the option for the user to choose file location and name seems like the best option. Yes the range can be set in the code:

Sub SaveSelection()
Dim data As Range


Set data = ActiveSheet.Range("A1:I95")
   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

 

[GARY_DANIEL]

Cell selection on each page as follows...

A1:I144 for DETAILED QUOTE (sheet 4)
A1:I48 for QUOTE (sheet 3)

 

[GARY_DANIEL]

Cell selection on each page as follows...

A1:I144 for DETAILED QUOTE (sheet 4)
A1:I48 for QUOTE (sheet 3)

I just change the range within the code right?

Thank you once again!

 

[Caleeco]

I just change the range within the code right?

Thank you once again!

No problem, if you want the range to change automatically depending on which sheet is active... you can add an IF statement up top:

Sub SaveSelection()
Dim data As Range


If ActiveSheet.Name = "QUOTE" Then
    Set data = ActiveSheet.Range("A1:I48")
Else
    Set data = ActiveSheet.Range("A1:I144")
End If


   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 it works
Thanks

 

[GARY_DANIEL]

Works great!
Thank you for you time and help on this.

Enjoy your weekend!

Kind regards,
Gary.

 

[Caleeco]

Works great!
Thank you for you time and help on this.

Enjoy your weekend!

Kind regards,
Gary.

No worries, you're more than welcome!

And you mate, 3-day weekend here I come! Have a good one

Regards
Caleeco