VBA for Save as PDF & Save file help

T

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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
 
Upvote 0
dnorm said:
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
Click to expand...
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?
 
Upvote 0
tropiqz said:
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?
Click to expand...
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..
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Open Hyperlink Once saved
Replies
2
Views
446
fourdragons
F
J
  • Solved
VBA Code to create pdf and name it based on criteria
Replies
5
Views
1K
Fluff
Fluff
tlc53
  • Question Question
VBA Save All Relevant Report into One Document
Replies
5
Views
660
tlc53
tlc53
JoshLyman
  • Solved
Save specific sheet as PDF
2
Replies
11
Views
2K
tahmed5835
T
R
  • Solved
VBA to Save As PDF Tweak
2
Replies
13
Views
2K
rodl66
R

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top