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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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,101,935
Messages
5,483,781
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top