Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range from a list
i am importing a list and i only need part of the list for
my report. my list contains data with columns id, acct, security, coupon, maturity, principal. i only need to work with data that has an id of 1-5. a lot of the data in the list doesn't have an id. so after i sort the list i only need to select the data with id 1-5 then copy that selection and paste into sheet 2. can't figure out a vba solution to this. can anyone one give guidance. tks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range from a list
just apply an autofilter to the data
Data=filter=Autofilter in the id column, select 1-5 from the dropdown, select all the data and copy, then go to the destination and do Edit=Paste Turn on the macro recorder while you do this and you will have tha basic code you need. You don't need to sort. Only the visible cells will be copied and pasted by default. -- Regards, Tom Ogilvy "Y.Kim" wrote in message ... i am importing a list and i only need part of the list for my report. my list contains data with columns id, acct, security, coupon, maturity, principal. i only need to work with data that has an id of 1-5. a lot of the data in the list doesn't have an id. so after i sort the list i only need to select the data with id 1-5 then copy that selection and paste into sheet 2. can't figure out a vba solution to this. can anyone one give guidance. tks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range from a list
some items have id 1 or 2 or 3 or 4 or 5..id "1-5" doesn't
exit. and my import list changes in size daily..if it was the same size then the macro recorder would be fine, but my list size changes. i sort by id so i can get items with either id 1, 2, 3, 4, 5 first on top of the list. then i usually delete the remaining rows without id seeking a way to automate this procedure. thank you, -----Original Message----- just apply an autofilter to the data Data=filter=Autofilter in the id column, select 1-5 from the dropdown, select all the data and copy, then go to the destination and do Edit=Paste Turn on the macro recorder while you do this and you will have tha basic code you need. You don't need to sort. Only the visible cells will be copied and pasted by default. -- Regards, Tom Ogilvy "Y.Kim" wrote in message ... i am importing a list and i only need part of the list for my report. my list contains data with columns id, acct, security, coupon, maturity, principal. i only need to work with data that has an id of 1-5. a lot of the data in the list doesn't have an id. so after i sort the list i only need to select the data with id 1-5 then copy that selection and paste into sheet 2. can't figure out a vba solution to this. can anyone one give guidance. tks. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range from a list
When you do your filter (once manually), you'll see that you can use Custom.
Inside that option, you can specify "greater than or equal to" 1 AND "less than or equal to" 5. Record that macro when you do it and you'll see the code. "Y.Kim" wrote: some items have id 1 or 2 or 3 or 4 or 5..id "1-5" doesn't exit. and my import list changes in size daily..if it was the same size then the macro recorder would be fine, but my list size changes. i sort by id so i can get items with either id 1, 2, 3, 4, 5 first on top of the list. then i usually delete the remaining rows without id seeking a way to automate this procedure. thank you, -----Original Message----- just apply an autofilter to the data Data=filter=Autofilter in the id column, select 1-5 from the dropdown, select all the data and copy, then go to the destination and do Edit=Paste Turn on the macro recorder while you do this and you will have tha basic code you need. You don't need to sort. Only the visible cells will be copied and pasted by default. -- Regards, Tom Ogilvy "Y.Kim" wrote in message ... i am importing a list and i only need part of the list for my report. my list contains data with columns id, acct, security, coupon, maturity, principal. i only need to work with data that has an id of 1-5. a lot of the data in the list doesn't have an id. so after i sort the list i only need to select the data with id 1-5 then copy that selection and paste into sheet 2. can't figure out a vba solution to this. can anyone one give guidance. tks. . -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range from a list
If you select a single cell in the range, then apply the autofilter, Excel
will apply it to the "currentregion", so changing size doesn't affect it. -- Regards, Tom Ogilvy Dave Peterson wrote in message ... When you do your filter (once manually), you'll see that you can use Custom. Inside that option, you can specify "greater than or equal to" 1 AND "less than or equal to" 5. Record that macro when you do it and you'll see the code. "Y.Kim" wrote: some items have id 1 or 2 or 3 or 4 or 5..id "1-5" doesn't exit. and my import list changes in size daily..if it was the same size then the macro recorder would be fine, but my list size changes. i sort by id so i can get items with either id 1, 2, 3, 4, 5 first on top of the list. then i usually delete the remaining rows without id seeking a way to automate this procedure. thank you, -----Original Message----- just apply an autofilter to the data Data=filter=Autofilter in the id column, select 1-5 from the dropdown, select all the data and copy, then go to the destination and do Edit=Paste Turn on the macro recorder while you do this and you will have tha basic code you need. You don't need to sort. Only the visible cells will be copied and pasted by default. -- Regards, Tom Ogilvy "Y.Kim" wrote in message ... i am importing a list and i only need part of the list for my report. my list contains data with columns id, acct, security, coupon, maturity, principal. i only need to work with data that has an id of 1-5. a lot of the data in the list doesn't have an id. so after i sort the list i only need to select the data with id 1-5 then copy that selection and paste into sheet 2. can't figure out a vba solution to this. can anyone one give guidance. tks. . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a list in 1 cell by selecting an item from another list in | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
How to create list on another tab by selecting qty from big list? | Excel Worksheet Functions | |||
Condensing a list/range with blank cells to a new list/range without blanks | Excel Worksheet Functions | |||
Selecting A Range | Excel Programming |