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

	A	B	C
1
2	[ heading text ]	[ heading text ]	[ heading text ]
3	[=Sheet2!C2]</SPAN> <TBODY> </TBODY>	[=Sheet2!A2]</SPAN>	[=Sheet2!D2]</SPAN>
4
5	[ heading text ]</SPAN>		[ heading text ]</SPAN>
6	[=Sheet2!H2]</SPAN>		[=Sheet2!F2]</SPAN>
7
8	[ heading text ]
9	[=Sheet2!B2]</SPAN>
10
11	[ heading text ]		[ heading text ]</SPAN>
12	[=Sheet2!G2]</SPAN>		[=Sheet2!E2]</SPAN>

<TBODY>
</TBODY>

Sheet 2

	A	B	C	D	E	F	G	H	I
1	[ heading ]	[ heading ]	[ heading ]	[ heading ]	[ heading ]	[ heading ]	[ heading ]	[ heading ]	[ heading ]
2	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx
3	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx
4	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx	xxxx

<TBODY>
</TBODY>