Copying from a Dynamic Source!

[Buyone]

Hello people! I'm back with another quandry to test the skills of the excel knowledgeable out there! (my last problem was solved "whilst blindfolded and juggling flaming chainsaws" it was that easy by Charlize and Xld)
I've got a costs sheet which I update twice weekly. The costs come in two different excel spreadsheets and I save down what I need to a long list.
Now I tried to just save them down as the same file name and have links to the file I used. But for some reason the clever people sending me the info move it around (only by a column or row) but still enough to make my link useless!
Does anyone have an idea where I could start? I thought of the go find macro but the sheets also include last yrs costs above with the same titles etc!

Many thanks

[Bob Phillips]

Not a solution but a word of advice. DON'T link Excel files, it creates problems (but you already know that <g>).

[Charlize]

Do they use the same columnheaders (always) at the same row ? Or do they also shift the rows.

[Buyone]

Hi,

Yes Xld, I know to my cost the problems linking files can cause. Think I lost at least three days of my life re tracing corrupt data!

Charlize, they also move rows. Good planning eh? And just to add that little bit more of a challenge, the column headers are always the same but they also have last yrs cost and the variance above. Giving three sets of data with the same titles!
Told you this would challenge you a little more!

:yes

[Charlize]

Well, if it's always the last set of headers (the 3rd) we could look for three times at the first headercolumn (the value of that column) in the worksheet. If we know the beginning address of that address, we hopefully wish that the person who makes this, doesn't use extra columns between the headers.

I mean. First header is lets say Name. If we select the whole worksheet and we look three times for Name (and First row doens't contains any header --- Find skips this row the first time when it's executed ---) we have the start address of the 3rd header row. We only do a search on the filled in values of the range (the selected worksheet). Maybe we can do this with specialcells function.