0

I am trying to assign a Macro to a button that will save a plot and a few ranges of values to a single PDF file, that the user names (and chooses the save location for) themselves. So far, I can call open the save as box, from another question I saw answered here, but I'm unsure how to specify what exactly I want saved.

Sub Save()

Dim bFileSaveAs As Boolean
bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show
If Not bFileSaveAs Then MsgBox "User cancelled", vbCritical
Dim chrt As Chart
Dim rng As Range

End Sub

I've attached a picture of the workbook, and circled the parts I want saved, for help in seeing what I'm going for. Thanks!

enter image description here

Emily
  • 15
  • 3

1 Answers1

0

If you want to save the multiple ranges as PDF then you can try this code:

    Sub CreateMultiRangeOnePagePDF()

  Dim RangeArray() As Variant
  Dim x As Long, LR As Long
  Const RngPad As Long = 2 'set to number of rows between ranges
  
  RangeArray = Array("Sheet1!A1:D30", "Sheet1!E1:H30", "Sheet1!I1:L30")
  
  Application.ScreenUpdating = False
  
  Sheets.Add After:=Sheets(Sheets.Count)
  
  For x = 0 To UBound(RangeArray)
  
    LR = Sheets(Sheets.Count).Cells(Rows.Count, 1).End(xlUp).Row
    
    If LR <> 1 Then LR = LR + 1 + RngPad
    
    Range(RangeArray(x)).Copy
    
    Sheets(Sheets.Count).Cells(LR, 1).PasteSpecial Paste:=xlPasteValues
    
    Selection.PasteSpecial Paste:=xlPasteFormats
    
    Application.CutCopyMode = False
    
  Next x
  
  Sheets(Sheets.Count).ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="Z:\Test.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    
  Application.DisplayAlerts = False
  Sheets(Sheets.Count).Delete
  Application.DisplayAlerts = True
  
End Sub
Rajput
  • 605
  • 3
  • 12
  • Will this allow the user to choose where they save the file? Also, does this only save the ranges- do you know if it's possible to save the graph to the same pdf? Thanks for the help! – Emily Jun 17 '21 at 17:48
  • No it does not allow the user to save the file but it saves the file where Excel file is already saved. Yes it only save the ranges you can paste the `Chart` range into code it will be saved too. – Rajput Jun 17 '21 at 17:52
  • When I try to implement this code, the second last section gives me a run-time 1004 error, any ideas why? Thanks! – Emily Jun 25 '21 at 16:55
  • Because you have not set this `Filename:="Z:\Test.pdf", _` section – Rajput Jul 15 '21 at 12:37