VBA to select and export multiple sheets based on values (separated by comma/space) in a single cell

[linz]

Hi,

Brand new to vba and after a bit of searching around, I haven't quite found what I'm trying to do.

1. My workbook has a sheet for all 50 states titled by 2-letter abbreviations.
2. On its own sheet ("export"), I have a table that's named "ExportTable" with 10 rows and 3 columns.
3. Each row has specific states assigned to it in Col B (it may be one state, 2 states, 3 states, 12 states, etc.)
4. I'd like to have all of the rows turned into individual pdf files and each named whatever is in col C (3rd col).


Col A (description) | Col B (state abbreviations separated by space and comma) | Col C (filename for each row)

first 3 example rows:

nw	wa, or	nwjan.pdf
ne	me, nh, vt	nejan.pdf
mid	il, in	midfeb.pdf

Using the first 3 rows as an example, I'd like to have the following as the output of this macro:

1 file named "nwjan.pdf" with the sheets named "wa" and "or" in it.
1 file named "nejan.pdf" with the sheets named "me", "nw", and "vt" in it.
1 file named "midfeb.pdf" with the sheets named "il" and "in" in it.
Etc.

---
Then as a different option to the above example where I'd like to run the macro and have it create files for each row in my table ("ExportTable), I'd like to be able to select specific rows to export. Maybe I don't need to export all of them at once, but I need to export row 2, row 5, and row 10. But this is a "nice to have". To keep it simple, maybe I have a cell all by itself (F3) where I list the values in Col A to determine which rows to export. So F3 = "nw, mid" and instead of exporting all rows into individual pdfs, I only want the rows for "nw" and "mid".

Any help would be appreciated. I know how to select one cell, but I don't know how to parse out the different sheet names in one cell.

Thanks!

 

[AlphaFrog]

Try this. Change the path where the pdf files are saved to your liking.

[color=darkblue]Sub[/color] State_PDF_Files()
    
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range, a [color=darkblue]As[/color] [color=darkblue]Long[/color], rng [color=darkblue]As[/color] Range
    
    [color=darkblue]Do[/color]
        a = Application.InputBox("1.) Save all listed rows " & vbLf & _
                                 "2.) Selected rows only ", _
                                 "Save as PDF Files", Type:=1)
        [color=darkblue]If[/color] a = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]'User canceled[/color]
        [color=darkblue]If[/color] a = 1 [color=darkblue]Or[/color] a = 2 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
    [color=darkblue]Loop[/color]
    
    [color=darkblue]If[/color] a = 1 [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    [color=darkblue]Else[/color]
        [color=darkblue]Set[/color] rng = Intersect(Selection.EntireRow, Range("B2", Range("B" & Rows.Count).End(xlUp)))
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] rng
        [color=darkblue]If[/color] a = 1 [color=darkblue]Or[/color] (a = 2 And [color=darkblue]Not[/color] Intersect(rng, cell) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color]) [color=darkblue]Then[/color]
            Sheets(Split(cell.Value, ", ")).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=[COLOR=#ff0000]"C:\MyFolder\"[/COLOR] & cell.Offset(, 1).Value, _
                                            Quality:=xlQualityStandard, _
                                            IncludeDocProperties:=True, _
                                            IgnorePrintAreas:=False, _
                                            OpenAfterPublish:=[color=darkblue]False[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] cell
    
    rng.Parent.Select
    
End [color=darkblue]Sub[/color]

 

[linz]

Hi. Thank you!

I apologize if this is obvious, but I don't know what to put as the input when the box pops up. I tried selecting the specific cells and rows, but it says "invalid number."

 

[AlphaFrog]

Hi. Thank you!

I apologize if this is obvious, but I don't know what to put as the input when the box pops up. I tried selecting the specific cells and rows, but it says "invalid number."

If you want all the used rows in column B, enter 1
If you want only the selected rows, select them first then run the macro and enter 2

 

[linz]

Oh ok, thanks for clarifying.

I'm not sure why but either option, it only prints one row.

Using my 3 example rows, option 1 only exports one pdf, but it does include the correct states listed in the single cell (b4).
Option 2 only exports one pdf, which is always the last row if more than one are selected.

How can I get each row to have a pdf created all at once? Loop it to go through each row until either it hits an empty row or the end of a named range. I'm not sure which makes more sense.

thanks!