I have done all the thinking but now i gave up, can anyone pleae help me.What i have are four sheets with the same information but different locations.
the headings are the same for all first 3 sheets
A1 Agent name
B1 Agent address
C1 Agent city.......

Sheet 1 is Zone1
Sheet 2 is Zone2
Sheet 3 is Zone3

now what i want to achieve is on sheet 4
first row tells me the same headings as on any of the previous sheets (as they are all the same). Then gives me the label "Zone 1" and then lists all the entries in sheet 1, when finished gives me the label "Zone 2" and then lists all the entries in sheet 2 and finally the same with sheet 3.
If possible can be a macro that i can attach to a command button, so when needed i can run the summary report and it coppies all the information in the summary sheet.
the information on the individual sheets changes frequently, and i usually run the summary report twice a month.

Any thing, code. guidance would help. thanks in advance.

It's really hard to figure out (well, not that hard... ) what you're trying to do.

Post your Workbook. That will Help immensly!

And at first glance, look at VLOOKUP in the Excel Helpfile - It's a bomber formula that might/probably will fit your needs.

I don't think you'll need VBA with this one, but like I said, post an example so we can whack away at it and get you an answer.

Hope that helps,

Smitty

Check out Consolidate - sounds like it might work for your situation.

ok this download has 2 macros in it, so if you turn them off i have cut and pasted the text to a sheet

i just recorded some macros and it might work ok depending on the consistancy of the data to be used.


good luck
ken