I am new to vba and i was wondering if anyone could help me with this issue:

I am trying to copy a value from sheet 1 to sheet 2 and i would like it to be based on the contents in a cell on sheet 2. So, on Sheet 2 i have ~30 titles in column b and i would like to, for each one of them, copy a value from sheet 1. So, in sheet 1 I have ~245 rows of data and the titles are dispersed among the rows. Example i have a title called "TOTALS: THEME U" in cell A and the value that i want to have copied to sheet 2 is in the last cell to the right. One of the problems is that i have blank cells between the title and that value!

On sheet 2 one of the titles is called "U: Catastrophe & Humanity". All titles on sheet 2 are in column b and the value i would like to paste would go in column C. So, i would like to copy the values on sheet 1 to sheet 2 based on the titles: sheet 2 title: "U: Catastrophe & Humanity" and sheet 1 title: "TOTALS: THEME U". I would like to copy the values for all ~30 titles.

thanks for your help
Troy