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

[ixruiz]

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!

 

[DanteAmor]

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:

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
--------------​

 

[ixruiz]

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.

 

[DanteAmor]

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

 

[ixruiz]

Thanks for your help!