Hi,
I'm totally new to macros in Excel and I'm hoping there's an easy
solution to my problem. Any help is much appreciated....

Here's my problem:

I have a workbook.
*Each sheet in the workbook is a day in a tour itinerary, formatted in
such a way that it'll look pretty when printed out.
*Each sheet is layed out in the same way, eg. Cell D4 is the venue
name, Cell F5 is the street address, etc.
*The number of sheets may change from tour to tour

I want to end up with a CSV file in which each line is the relevant
data from each sheet. My plan was to do the following:

*create 2 new sheets at the end of the workbook (I can do this bit).
Let's say they're sheet25 and sheet26
*on the first row of the first new sheet type the CSV headers across
the top (so A1 = "venue_name", A2="venue_phone", A3="venue_postcode",
etc)
*on the row beneath enter the relevant reference cell for the data I
need (so if the venue name appears in cell D4 on each sheet then cell
B2 will say "D4")

I want the macro to act on the second new sheet and do the following:
* figure out how many columns contain information (or I could just
write this value in another cell which the macro refers to)
*for all cells in row A which contain information - set sheet26.Ax
equal to sheet25.Ax (i.e. copy the first header row)

*then something like....

for ($i=0 ; $i<FinalSheet ; $i++){
for ($j=0 ; $j<FinalHeaderCol ; $j++){
Cell(row=A+$i , col=$j) = Sheet($i).Cell(ref = sheet25.B$j) //
so using the value written under the header row as a reference for the
cell to take the value from

}
}



I wish I could explain it better. I'm sure it's not a difficult task,
my problem is knowing the right syntax to use and how to take a value
from a Cell and use that as a reference for the lookup of another
cell.


Thanks for your help,

Joe