Macro to extract data

[Bill A]

I'm dealing with a weekly list that is broken into various segments,
separated by a header line. I'm trying to build a macro that will copy
all the data in say the first segment to another sheet for sorting, etc.
..
My previous Lotus experience (say 6 years ago) would let me do a
"end", "dn arrow" to get to the next header line, then copy back up to
capture my data. When I do that with excel in captures the cell
address at the end of the "end"+"dn arrow" sequence and stores that
cell address as part of the macro (eg. G51). If I use the macro on a
new list where the next header line is at say row 45, the macro will
still go to line 51 and copy from that point.
Maybe my "end , down" is the wrong approach. Any suggestions??
Thanks

 

[Otto Moehrbach]

Bill
If you use End - Down on a continuous column of data (header rows are
filled) Excel will go to the last entry in the column. Maybe I'm missing
what you are saying.
If your data is continuous and you want to copy (or whatever) just a
portion of it with VBA, you will have to write the code so that Excel can
find the extremities of what you want copied. For instance, if you want to
copy from here to the next header row, but not the header row itself, you
have to use a search routine to find the next header row. For instance, say
the next header in a column is "Header1", you would write code to search for
"Header1" and to start the search after some cell of your choosing. That
way, Excel will find only the next header even if the header text is
repeated in the data. Then you can write the code to copy just what you
want.
Not knowing any more about your data layout than what you said, I can't
give you any more. Post back with more details about your data if you need
more. HTH Otto

 

[Bill A]

Thanks for the response. I wasn't clear about my data layout. My headers
that separate segments only occupy 3 cells horizontal so I went to
column "D" in the data & did "End-Dn" so it would stop when it came to
the open cell in the header row. If the number of rows of data in the
first segment changed ,the macro would still go to the original
stopping point in column D. I hope that is more clear.
Meanwhile I'll try to incorperate the search routine you suggested.
Thanks a lot.

 

[Otto Moehrbach]

Bill
If you wish, send me a small file direct via email showing your data
layout and an explanation of what you want to happen (copy what to where,
followed by copying another what to another where, etc.). I'll work up some
code for you. My email address is (e-mail address removed). Remove the "nop"
from this address. HTH Otto