VBA Code to print to PDF only Certain Worksheet and Save to a specific Folder.

ixruiz

New Member
Joined
Jun 5, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am new to macros and VBA and need some help. I am also new to this forum and I am looking for any help or ideas that you might have.

I have a workbook with 50+ worksheets. I have to find each worksheets that contains the same location and save it to a folder that uses the name of Manager. Each Worksheet contains the name of the geographical location and the name of the building on Cell M1. On Cell N1 I have the name of the Manager's name. On my C:\ Drive I have a folder with the name of each manager. So, for example, my first three tabs have the location of CM126 RD1111 (on cell M3) and the Manager's name is John Smith (on cell N1). I need to print these three worksheet to one PDF file and save them to my folder Name C:\John Smith and so forth. Any help will be greatly appreciated!
 

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.
Hi @ixruiz

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

1. If I understood correctly you have an initial folder, for example C:\​
In the macro I put C:\Work, set to the name of your home folder.​
2. Inside that folder should be the manager folders. But the macro has a plus, if the manager folder does not exist, the macro creates it.​
3. You didn't mention how to name the pdf file, the macro is saving the pdf file with the location name.​
4. Each combination of location and manager data will generate a pdf file, it can be a sheet or several sheets of your excel file.​

Try the following code:
VBA Code:
Sub savepdf()
  Dim dic As Object
  Dim sh As Worksheet
  Dim ky As Variant, ary As Variant
  Dim a As String, b As String, c As String, itm As String, sPath As String, fName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  sPath = "C:\work\"     'initial folder
  
  Set dic = CreateObject("Scripting.Dictionary")
  For Each sh In Sheets
    a = sh.Range("M1").Value    'Location
    b = sh.Range("N1").Value    'Manager
    If a <> "" And b <> "" Then
      c = a & "|" & b
      dic(c) = dic(c) & sh.Name & ","
    End If
  Next
  
  For Each ky In dic.keys
    a = Split(ky, "|")(0)       'Location
    b = Split(ky, "|")(1)       'Manager
    If Dir(sPath & b, vbDirectory) = "" Then
      MkDir sPath & b
    End If
    fName = sPath & b & "\" & a & ".pdf"
    
    itm = dic(ky)
    itm = Left(itm, Len(itm) - 1)
    ary = Split(itm, ",")
    Sheets(ary).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName, Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  Next
  Sheets(1).Select
  MsgBox "Pdf files created"

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
Dante Amor,
This will work nicely.
1) that worked great! We will be going to One Drive soon, I understand that the path will need to be changed, but is there anything else I need to be aware of?
2) That is nice that it works this way.
3) I think I will use the Location as the name of the PDF.
4) That is great!

Thank you so much for your help! This will save me a days' worth of work.
 
Upvote 0
We will be going to One Drive soon, I understand that the path will need to be changed, but is there anything else I need to be aware of?
I have no way to test on OneDrive, you'll have to do your research when the time comes.


By the way, the second Backslash should not be.
sPath = "C:\work\" 'initial folder

It should be like this:
sPath = "C:\work" 'initial folder

:cool:
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

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