Need macro to sequentially populate worksheet from other worksheet + save as .pdf (Excel 2007)

[NBMG1]

Hello Forum:
I am just getting started with macros, could not make this work, and would greatly appreciate your help and expertise.

The task we are trying to execute is essentially populating a chart (Sheet 1) repeatedly with data from sequential rows in a table (Sheet 2), and saving each new result as a .pdf with a certain cell value as the filename, so that each row of Sheet 2 is represented by a unique .pdf form. The worksheets are in the same workbook.

Here is the process as we've doing it manually:

- FIND [row number from Sheet 2] in Sheet 1 ((Sheet 1 cells already contain formulas to fill cells with values from Sheet 2; see below))
- REPLACE with [next sequential row number from Sheet 2]
- SAVE Sheet 1 as .pdf with Cell A3 value as file name
- Repeat (to last row of Sheet 2)

Here is a simplified version of what the worksheets look like:

Sheet 1
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][ heading text ]
[/TD]
[TD][ heading text ]
[/TD]
[TD][ heading text ]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][TABLE="width: 72"]
<TBODY>[TR]
[TD][=Sheet2!C2]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][=Sheet2!A2]</SPAN>
[/TD]
[TD][=Sheet2!D2]</SPAN>
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][ heading text ]</SPAN>
[/TD]
[TD][/TD]
[TD][ heading text ]</SPAN>
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][=Sheet2!H2]</SPAN>
[/TD]
[TD][/TD]
[TD][=Sheet2!F2]</SPAN>
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][ heading text ]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][=Sheet2!B2]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][ heading text ]
[/TD]
[TD][/TD]
[TD][ heading text ]</SPAN>
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][=Sheet2!G2]</SPAN>
[/TD]
[TD][/TD]
[TD][=Sheet2!E2]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
Sheet 2
[TABLE="class: grid, width: 500, align: right"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[TD][ heading ]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[TD]xxxx
[/TD]
[/TR]
</TBODY>[/TABLE]