How to iterate lines of excel to save as pdf w/ updated filename

Hi guys,

Beginner here, so sorry for the mistakes. I hope that this is not too much trouble. My goal is to go through each line of code in Excel Workbook, and ExportAsFixedFormat each line as a separate, distinct pdf, with a constantly-changing cell value as filename. I have been at this for about a week and a half. At first, I was using Selection to take each Row I click on and print that as PDF, but this is too hard. I have seen code that does this for one pdf, but I need a lot of different pdfs. I am trying to take the entire spreadsheet and go line by line now, taking a pdf for each row, while also trying to print the labels at the top.

Here's an example:

Name Weight
Zeke 20
Dylan 14

And it would give, as two separate pdfs with filenames "Zeke" and "Dylan" respectively, with the content:

Name Weight
Zeke 20

And the other would be

Name Weight
Dylan 14

What I have so far is this (please do not run on your own stuff, as it currently will spam your printer to print blank pages).

Screen Shot 2020-01-19 at 12.25.43 PM.png

Right now, none of my code works (as mentioned, it prints a bunch of blank pages). I'm going to keep working on it, but I've sort of hit a wall. How do I PageSetup, or change what will be saved as a pdf, without having the macro just print the page out? I'm totally confused.

Some ideas I have is using a separate worksheet, that I can use the "set" function to make it only contain the TitleRow and CounterRow of Worksheets("Sheet1"). Maybe I can use a for loop given a range, to go through each line until lastRow, if Counter is responsible for this total mess.

Any help or guesses would be greatly appreciated, even to point me in a better direction. Thank you!

-Qiu Ye

Thank you so much. For my own clarity, I am trying to piece together what happened here below. I really can't thank you enough, Leith!

-Create Folder path
-Give a worksheet to work in
-Set Rng as the CurrentRegion of Wks.Cells(1, "A") ... essentially meaning, the first cell of column A, but ensures that blank rows have no influence. Becomes important for the index For loop.
-Set the Wks Name as "print_area" which will be the Name Object referred to with "RefersToRange", and Rng is the Range Object that will be called on.
-Set the pdfRng, or the stuff that we want to print, as the value of 1, "A", and the offset, or the cell to the right. Need to use Intersect, as pdfRng is within Rng.
-Is Nothing essentially checks for stuff like, is the value of the cell 0 or "", and handles that by exiting the macro ... and, if it is non-zero or non-"", hides the pdfRng, so that you can only reveal the row of data you want to print.
-For loop indexs through each line of pdfRng, we make sure we go through each line by using the "Count" command
-Only the indexed row is un-hidden
-The File-name is set to the value of the indexed cell, using "Item", and is prepared to be a pdf
-Returns Range object referred to by a Name object, the Range object is Rng and Name object is "Print_area," and Rng is essentially the value of Cell(1, "A"). This is then export as pdf
-The indexed row is hidden. The "CurrentRegion" comes in handy here, since it will not pick up on the hidden row?
-We index the next cell, effectively going through the whole spreadsheet
-We unhide the rows at end, so that our data does not all get hidden after we execute the macro.

Again, thank you sincerely.

-Qiu ye