Convert sheet to pdf file and save to a different folder based on cell value

[dandy]

I am trying to make a tool that will save a selected portion of a sheet as a .pdf file and then save it to a folder based on the value of another cell.

Example:

Cell C2 contains the name: Doe, John
I would like to select a portion of the sheet, say A2:G14, convert it to a .pdf file and save it to the folder: c:\Users\Doe, John

In addition, I would want this tool to loop through and do it for every person listed in the sheet.

I do not have anything written at this point because I don't know where to start.

Thanks for any help!

 

[spydey]

dandy,

So maybe something like this?

Option Explicit

Sub GeneratePDF()
Dim Path As String
Dim Filename As String

Call SetPrintArea
Path = "C:\" & ActiveSheet.Range("C2").Value & "\"
Call MakeDirectory(Path)
Filename = ActiveSheet.Range("C2").Value
Application.DisplayAlerts = False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & ActiveSheet.Range("C2").Value & ".pdf"
Application.DisplayAlerts = True

End Sub

Sub SetPrintArea()
    Dim xRg As Range
    Dim xRgLRow As Long
    Dim xRgAddress As String
    On Error Resume Next
    xRgAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Select a range:", , xRgAddress, , , , , 8)
    xRg.Select
    xRgLRow = xRg.SpecialCells(xlCellTypeLastCell).Row
    xRgAddress = Left(xRg.Address(1, 1), Len(xRg.Address(1, 1)) - 2)
    xRgAddress = xRgAddress & xRgLRow
    ActiveSheet.PageSetup.PrintArea = xRgAddress
End Sub


Private Sub MakeDirectory(FolderPath As String)
Dim x, i As Integer, strPath As String
x = Split(FolderPath, "\")

For i = 0 To UBound(x) - 1
    strPath = strPath & x(i) & "\"
    If Not FolderExists(strPath) Then MkDir strPath
Next i

End Sub

Function FolderExists(FolderPath As String) As Boolean
On Error Resume Next

ChDir FolderPath
If Err Then FolderExists = False Else FolderExists = True

End Function

Take note, this does not loop through any names. Without knowing where the names are stored (same sheet, different sheet, same cell, different cell, etc.) and which ranges (same range you previously provided, different range, etc.) they correspond to, I am afraid I am not skilled enough to do such a thing yet.

However, if you can provide a bit more detail about where the other names and ranges are stored in relation to your workbook/worksheet(s), that would be helpful. I would like to assume that each name/range is on a different sheet (that would make looping through each of them easier), but that is just an assumption.

Also, I assumed (funny how I didn't want to assume above for the names/ranges, but am fine with doing so for the file name, hahahaha) that you want the file name of the PDF to be the name as found in C2.

Please note that an input box will appear asking you to select the range you want to only show in the PDF. This piece is manual.

If you need a loop for all names/ranges (sheets), then this might get a bit tedious to manually select each range every time, especially if there are many sheets you need to do this to ..... I apologize.

Also, I added a function so that if the folder doesn't exist where the file would be saved, it gets created. It saves headaches and debugging just to realize that the folder is missing.

Let me know what you think and if you have any ideas, suggestions, input, etc.

Hopefully this works for you or is at least close to what you are aiming for.

Take care!!

-Spydey

P.S. DISCLAIMER: I am just beginning in VBA so the code is probably very clumsy, clunky, and cluttered. I am sure that someone else will probably have a much cleaner, faster, efficient, and better way of doing it, if they do, I would love to see it and learn!!!!

 

[dandy]

Thanks, Spydey! This is a great start.

I updated the select range portion to be a little bit more simple since it will be the same range every time.

Sub SetPrintArea()
    Range("A2:G14").Select
End Sub

To answer your questions below (see answers in bold):

Take note, this does not loop through any names. Without knowing where the names are stored (same sheet, different sheet, same cell, different cell, etc.) and which ranges (same range you previously provided, different range, etc.) they correspond to, I am afraid I am not skilled enough to do such a thing yet.

However, if you can provide a bit more detail about where the other names and ranges are stored in relation to your workbook/worksheet(s), that would be helpful. I would like to assume that each name/range is on a different sheet (that would make looping through each of them easier), but that is just an assumption. The names are stored in a separate sheet (Column A, "Sheet2"). I would ideally like for this to loop through each name and save it to a separate folder with that persons name.

Also, I assumed (funny how I didn't want to assume above for the names/ranges, but am fine with doing so for the file name, hahahaha) that you want the file name of the PDF to be the name as found in C2. I actually updated your code a little bit to name the actual PDF file in the format "YEAR-XX" so that's all taken care of. Thanks again for giving me something to go off of.

Please note that an input box will appear asking you to select the range you want to only show in the PDF. This piece is manual. I addressed this above by simplifying the selection macro.

If you need a loop for all names/ranges (sheets), then this might get a bit tedious to manually select each range every time, especially if there are many sheets you need to do this to ..... I apologize.

Also, I added a function so that if the folder doesn't exist where the file would be saved, it gets created. It saves headaches and debugging just to realize that the folder is missing.

Let me know what you think and if you have any ideas, suggestions, input, etc.

Hopefully this works for you or is at least close to what you are aiming for.