Need help with organizing processes to proceed. Project / Challenge.

[noveske]

Need help organizing processes before beginning coding. Want to try coding it and seeing if this is possible. Have been tinkering and learning on my own. Looking for some direction. Thought of ways to pull this off the long hard way, but want to see and learn if there are better ways. Since there are multiple things going on, I feel I am overcomplicating it and overwhelming myself. Might be a bit much for one post. Might end up breaking it down step by step for each post.

Thanks!

Description of Sheets:
Sheet 1 has rows of data. A:H. The data is sorted and organized here.
Sheet 2 extracts data from Sheet 1 for better grouping. It just displays what's on Sheet 1 organized.
Sheet 3 is where I'm having trouble thinking of the proper order of steps to pull it off. (Screenshot below)
These are the 2 fields that need filled.
A: Location - Up to 15 locations.
B: Site

Requirements:
1. I want the workbook to fill out Sheet 3 with the data from Sheet 1. Each row fills one card. Each printable sheet contains 6 cards.
2. Card sheets should only contain cards with the same location. If the number of cards is not a multiple of 6, then there would be blank cards. So anything up to 6 cards is one sheet. 7-12 is 2 and so on. Does not matter if unfilled cards are visible or blank when printed. Location can be found in cells of column B on Sheet 2.
3. After the cards are filled, it is to save as a file to be sent to each unique location. Preferably .pdf.

So simply, if Sheet 1 has 20 rows:
Location1 shows 3 times.
Location2 shows 7 times.
Location3 shows 10 times.

The cards will be filled out then saved for each location.
Location1.pdf contains 1 page.
Location2.pdf contains 2 pages.
Location3.pdf contains 2 pages.

My logic and thoughts for a solution:
Data goes into Sheet1, data is organized and sorted here. (Solved)
Sheet2 extracts parts of cells in Sheet1. (Solved)

UNSOLVED: To fill in Sheet3, manually reference each cell on Sheet 2.
Looking for a way to automatically do this. Don't know where to start with this since it's not a constant. Then there is a gap of blanks cells to space the pages.

UNSOLVED: Separate and save sheets.
Change Sheet2 organize the data into columns. Then have each columns reference to the corresponding sheet.
Only thing I can think of is making separate sheets for each Location with the cards.
I'm pretty new and self learned to this point, so I'm not sure what's possible.
Maybe a way for the script to create a sheet for each unique location. Have a card template, then replicate the card on it's own to the sheet?

Screenshots (Sites won't match because I typed them in as an example. Locations aren't split by Location since nothing is scripted yet.):

Sheet 2:

Sheet 3:

 

[noveske]

Giving this a lot of thought.
Might just make all the sheets, then after sorting, I'll have it broken down by Location and placed on the side. Then have it do it for each sheet from there.

Then only have ranged sheets that have data in ranged cells save.
Now to figure out how it would know if there was data referenced or if it's just blank with formula.
This might work.

 

[Skyybot]

Are you trying to use Formulas or VBA?

 

[noveske]

Are you trying to use Formulas or VBA?

VBA.