Save a selection of cells in Excel and save to PDF

[Pam365]

Urgent help please, Save a selection of cells in Excel and save to PDF.

I have an excel file in which I have a VBA to "Save Invoice Both Ways and Clear" The macro I have saves the whole worksheet as "xlsx and a PDF" file.
I really need this macro to save only the selected cell range "A1:N116" within this worksheet, and save both ways. I'm not very good at Macro's and needed help to get my existing macro working. It works perfectly now. Any help adjusting my existing macro to copy a range of cells instead of the whole "active Sheet" would be of so much help.
Here is my existing macro....

Sub SaveRangeBothWaysAndClear()
Dim NewFN As Variant
NewFN = "\\Z:\Company files\PDF Invoice copies\Doc" & Range("N5").Value & "" & Range("N10").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
ActiveSheet.Copy
NewFN = "\\Z:\Company files\Excel Invoice Copies 2022\Inv" & Range("N5").Value & "" & Range("N10").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Range("N5").Value = Range("N5").Value + 1

Range("B10:K10,A11:K14,A15:C15,F15:K15,N4,N10:N15,A17:N19,C22:N23,C25:M30,C34:M37,C40:M45,A73:N76,A79:M100").ClearContents

End Sub

Thanks to anyone who can help

 

[Domenic]

Simply use the ExportAsFixedFormat method of the Range object instead of the Worksheet object...

Range("A1:N116").ExportAsFixedFormat . . .

Hope this helps!

 

[Pam365]

Simply use the ExportAsFixedFormat method of the Range object instead of the Worksheet object...

Range("A1:N116").ExportAsFixedFormat . . .

Hope this helps!

Hi. I'm sorry I don't understand. Where would I enter your line of code and do I type exactly what you have or what do I enter instead of ...[/code] thank you

 

[Domenic]

If I understood you correctly, you only want to export to PDF the specified range, not the worksheet, correct? If so, replace...

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

with

Range("A1:N116").ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

Does this help?

 

[Pam365]

Thank you I will give that a try as soon as I can. At work again on Thursday. I'll let you know if it works. Fingers crossed.

 

[Pam365]

Thank you I will give that a try as soon as I can. At work again on Thursday. I'll let you know if it works. Fingers crossed.

Hi Dominic
I've altered my VBA as you suggested and it works - thank you so much.
Could you also have a look at my file and help with saving the same range to a separate class file. It is also saving the whole worksheet at the moment.
Can't thank you enough - cheers Pam

 

[Pam365]

Hi Dominic
I've altered my VBA as you suggested and it works - thank you so much.
Could you also have a look at my file and help with saving the same range to a separate class file. It is also saving the whole worksheet at the moment.
Can't thank you enough - cheers Pam

Sorry for mis-spell. Trying to save as an excel xlsx file

 

[Domenic]

Try the following...

Sub SaveRangeBothWaysAndClear()

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ActiveSheet
    
    Dim sourceRange As Range
    Set sourceRange = sourceWorksheet.Range("A1:N116")

    Dim pdfFilename As String
    With sourceWorksheet
        pdfFilename = "\\Z:\Company files\PDF Invoice copies\Doc" & .Range("N5").Value & .Range("N10").Value & ".pdf"
    End With
    
    sourceRange.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
    Dim xlFilename As String
    With sourceWorksheet
        xlFilename = "\\Z:\Company files\Excel Invoice Copies 2022\Inv" & .Range("N5").Value & .Range("N10").Value & ".xlsx"
    End With
    
    Dim newWorkbook As Workbook
    Set newWorkbook = Application.Workbooks.Add(xlWBATWorksheet)
    
    sourceRange.Copy Destination:=newWorkbook.Worksheets(1).Range("A1")
    
    newWorkbook.SaveAs xlFilename, xlOpenXMLWorkbook
    
    newWorkbook.Close
    
    With sourceWorksheet
        .Range("N5").Value = .Range("N5").Value + 1
        .Range("B10:K10,A11:K14,A15:C15,F15:K15,N4,N10:N15,A17:N19,C22:N23,C25:M30,C34:M37,C40:M45,A73:N76,A79:M100").ClearContents
    End With

End Sub

Hope this helps!

 

[Pam365]

Try the following...

Sub SaveRangeBothWaysAndClear()

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ActiveSheet
   
    Dim sourceRange As Range
    Set sourceRange = sourceWorksheet.Range("A1:N116")

    Dim pdfFilename As String
    With sourceWorksheet
        pdfFilename = "\\Z:\Company files\PDF Invoice copies\Doc" & .Range("N5").Value & .Range("N10").Value & ".pdf"
    End With
   
    sourceRange.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
       
    Dim xlFilename As String
    With sourceWorksheet
        xlFilename = "\\Z:\Company files\Excel Invoice Copies 2022\Inv" & .Range("N5").Value & .Range("N10").Value & ".xlsx"
    End With
   
    Dim newWorkbook As Workbook
    Set newWorkbook = Application.Workbooks.Add(xlWBATWorksheet)
   
    sourceRange.Copy Destination:=newWorkbook.Worksheets(1).Range("A1")
   
    newWorkbook.SaveAs xlFilename, xlOpenXMLWorkbook
   
    newWorkbook.Close
   
    With sourceWorksheet
        .Range("N5").Value = .Range("N5").Value + 1
        .Range("B10:K10,A11:K14,A15:C15,F15:K15,N4,N10:N15,A17:N19,C22:N23,C25:M30,C34:M37,C40:M45,A73:N76,A79:M100").ClearContents
    End With

End Sub

Hope this helps!

Thanks again, I'll give it a try. It'll be next week before I can report back. Cheers