Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to extract data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to extract data
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" wrote in message ... 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 -- Bill A ------------------------------------------------------------------------ Bill A's Profile: http://www.excelforum.com/member.php...o&userid=31480 View this thread: http://www.excelforum.com/showthread...hreadid=511577 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to extract data
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. -- Bill A ------------------------------------------------------------------------ Bill A's Profile: http://www.excelforum.com/member.php...o&userid=31480 View this thread: http://www.excelforum.com/showthread...hreadid=511577 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help In Excel | Excel Discussion (Misc queries) | |||
import data using macro | Excel Discussion (Misc queries) | |||
enter data in cell which will start macro to move data to sheet2 | Excel Discussion (Misc queries) | |||
External data Macro Problem Excel 97 | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |