VBA for Save as PDF & Save file help

tropiqz

New Member
Joined
Aug 28, 2018
Messages
10
Hi Guys,

I have created a VBA which saves the active sheet of the specific workbook - this works perfectly but I also want to save the whole workbook alongside the PDF with the same filename as the PDF.

How can I fit this in on the same macro? Please see below code:

VBA Code:
Sub PDFActiveSheet()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "ddmmyy")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'set save name
strName = wsA.Range("N7").Value _
          & " - " & wsA.Range("N8").Value _
          & " - " & wsA.Range("N9").Value

'create default name for savng file
strFile = strName & "" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file 'currently set to active folder
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub
Super thanks in advance!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
79
HI tropipz

This is what I am running. mine is more complex in that it adds the date also, but hopefully you can see what info you need.

VBA Code:
Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.

   Dim month_folder As String
Dim Name As String
Dim Path As String


If Range("M1") = 1 Then
month_folder = "01 January"
End If

If Range("M1") = 2 Then
month_folder = "02 February"
End If

If Range("M1") = 3 Then
month_folder = "03 March"
End If

If Range("M1") = 4 Then
month_folder = "04 April"
End If

If Range("M1") = 5 Then
month_folder = "05 May"
End If

If Range("M1") = 6 Then
month_folder = "06 June"
End If

If Range("M1") = 7 Then
month_folder = "07 July"
End If

If Range("M1") = 8 Then
month_folder = "08 August"
End If

If Range("M1") = 9 Then
month_folder = "09 September"
End If

If Range("M1") = 10 Then
month_folder = "10 October"
End If

If Range("M1") = 11 Then
month_folder = "11 November"
End If

If Range("M1") = 12 Then
month_folder = "12 December"
End If

    Path = "\\CSHFS002\Dept$\Quality\07 Reports\03 Daily Quality Report\00 Report PDF Archive\" & month_folder

Name = Path & "\" & "DQR " & Format(Range("D1"), "yyyy.mm.dd. ddd") & ".pdf"

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

       
End Sub
Sub SAVE_REPORT()


Columns("A:G").Select
Range("G23").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

    Range("A4").Select

Dim month_folder As String
Dim Name As String
Dim Path As String


If Range("M1") = 1 Then
month_folder = "01 January"
End If

If Range("M1") = 2 Then
month_folder = "02 February"
End If

If Range("M1") = 3 Then
month_folder = "03 March"
End If

If Range("M1") = 4 Then
month_folder = "04 April"
End If

If Range("M1") = 5 Then
month_folder = "05 May"
End If

If Range("M1") = 6 Then
month_folder = "06 June"
End If

If Range("M1") = 7 Then
month_folder = "07 July"
End If

If Range("M1") = 8 Then
month_folder = "08 August"
End If

If Range("M1") = 9 Then
month_folder = "09 September"
End If

If Range("M1") = 10 Then
month_folder = "10 October"
End If

If Range("M1") = 11 Then
month_folder = "11 November"
End If

If Range("M1") = 12 Then
month_folder = "12 December"
End If

    Path = "\\CSHFS002\Dept$\Quality\07 Reports\03 Daily Quality Report\" & month_folder

Name = Path & "\" & Format(Range("D1"), "yyyy.mm.dd ddd") & ".xlsb"
    ActiveWorkbook.SaveAs Filename:=Name


End Sub
 

tropiqz

New Member
Joined
Aug 28, 2018
Messages
10
HI tropipz

This is what I am running. mine is more complex in that it adds the date also, but hopefully you can see what info you need.

VBA Code:
Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.

   Dim month_folder As String
Dim Name As String
Dim Path As String


If Range("M1") = 1 Then
month_folder = "01 January"
End If

If Range("M1") = 2 Then
month_folder = "02 February"
End If

If Range("M1") = 3 Then
month_folder = "03 March"
End If

If Range("M1") = 4 Then
month_folder = "04 April"
End If

If Range("M1") = 5 Then
month_folder = "05 May"
End If

If Range("M1") = 6 Then
month_folder = "06 June"
End If

If Range("M1") = 7 Then
month_folder = "07 July"
End If

If Range("M1") = 8 Then
month_folder = "08 August"
End If

If Range("M1") = 9 Then
month_folder = "09 September"
End If

If Range("M1") = 10 Then
month_folder = "10 October"
End If

If Range("M1") = 11 Then
month_folder = "11 November"
End If

If Range("M1") = 12 Then
month_folder = "12 December"
End If

    Path = "\\CSHFS002\Dept$\Quality\07 Reports\03 Daily Quality Report\00 Report PDF Archive\" & month_folder

Name = Path & "\" & "DQR " & Format(Range("D1"), "yyyy.mm.dd. ddd") & ".pdf"

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

      
End Sub
Sub SAVE_REPORT()


Columns("A:G").Select
Range("G23").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

    Range("A4").Select

Dim month_folder As String
Dim Name As String
Dim Path As String


If Range("M1") = 1 Then
month_folder = "01 January"
End If

If Range("M1") = 2 Then
month_folder = "02 February"
End If

If Range("M1") = 3 Then
month_folder = "03 March"
End If

If Range("M1") = 4 Then
month_folder = "04 April"
End If

If Range("M1") = 5 Then
month_folder = "05 May"
End If

If Range("M1") = 6 Then
month_folder = "06 June"
End If

If Range("M1") = 7 Then
month_folder = "07 July"
End If

If Range("M1") = 8 Then
month_folder = "08 August"
End If

If Range("M1") = 9 Then
month_folder = "09 September"
End If

If Range("M1") = 10 Then
month_folder = "10 October"
End If

If Range("M1") = 11 Then
month_folder = "11 November"
End If

If Range("M1") = 12 Then
month_folder = "12 December"
End If

    Path = "\\CSHFS002\Dept$\Quality\07 Reports\03 Daily Quality Report\" & month_folder

Name = Path & "\" & Format(Range("D1"), "yyyy.mm.dd ddd") & ".xlsb"
    ActiveWorkbook.SaveAs Filename:=Name


End Sub
Thanks for that, is that one macro or two individual? i.e. do you have a button (or function) you press which does both pdf & save or is there a separate button for both?
 

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
79
Thanks for that, is that one macro or two individual? i.e. do you have a button (or function) you press which does both pdf & save or is there a separate button for both?
It is 2 macros that are part of a more complex group, that once finished saves and closes the files. I have to generate a report daily, so it is all started from a button click.

Which is easy if that is what you are looking for.
Sub run ()

Marco1 (your first step you need completing)

Macro2 (the next step)

Save pdf macro (publishes sheet to pdf)

Save file macro (saves the file)

Close file (once finished closes the excel file)

End sub

You can build a very complex routine by breaking each macro down into smaller steps then lost the order you want them to happen in another macro..
 

Watch MrExcel Video

Forum statistics

Threads
1,102,695
Messages
5,488,328
Members
407,634
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top