Name PDF from Filter Criteria Row

[tanyamc]

Many thanks to p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal) for the help with this project so far in this thread: http://www.vbaexpress.com/forum/showthread.php?69297-Total-filtered-rows-Range-of-object-Global-failed

Starting a new thread per the rules for the next piece.

I have a worklist that filters a raw data file and creates PDFs for each row in the worklist.

I need to customize the name for the PDFs. (They are currently being named based on the filter criteria cell, Funded Program, which is only part of the desired name.)

The names will come from the worklist file row containing the cell that is being used to filter the detail file.

The file name needs to be:

[Sales Order Number]_[Funded Program]_SD_[Fiscal Year]_[FISCAL PERIOD]

This code works for the first one, but I need to increment the row number in the 'set' lines to match the iLastRow_unique. I'm not sure how best to do that.

Many thanks!


For Each Cell In UniqueRng

ListObj.Range.AutoFilter Field:=4, Criteria1:=Cell

'create variables for file names
Dim SO As Range
Dim FY As Range
Dim FM As Range


'these need to increment with loop through the iLastRow_unique
Set SO = wbk_worklist.Sheets("SD_List").Range("B2")
Set FY = wbk_worklist.Sheets("SD_List").Range("C2")
Set FM = wbk_worklist.Sheets("SD_List").Range("D2")


Name = DirectoryLocation & "\" & SO.Value & "_" & Cell.Value & "_SD_" & FY.Value & "_" & FM.Value & ".pdf"

[p45cal]

I've not opened your attached files so this is a guess and is untested, but you should be able to tweak.
I'd ditch the range variables and make them strings:
SO = Cell.Offset(, 1).Value
FY = Cell.Offset(, 2).Value
FM = Cell.Offset(, 3).Value
Name = DirectoryLocation & "\" & SO & "_" & Cell.Value & "_SD_" & FY & "_" & FM & ".pdf"


or lose the variables altogether with:
Name = DirectoryLocation & "\" & Cell.Offset(, 1).Value & "_" & Cell.Value & "_SD_" & Cell.Offset(, 2).Value & "_" & Cell.Offset(, 3).Value & ".pdf"
Hopefully there are no disallowed characters in your file name.

[tanyamc]

Thanks. These are same files you helped me with, just updated with the code solution you gave me.

I'll try those options!

[tanyamc]

I went with the string method as I find it easier to 'follow' for future reuse and edits. Worked great! Thanks and thanks again!