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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
81
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
81
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..
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,540
Messages
5,511,928
Members
408,868
Latest member
AndrewLeonard

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top