Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still having Filter/Copy problems
Tom answered my original post titled, "Filter and Copy?", but I'm still
having problems with it. My code to Filter and Copy data from one sheet and transfer it to another is: Sub Advanced_Filter() Range("A5:J500").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("E1:E2"), _ CopyToRange:=Range("Print!A5"), _ Unique:=True Sheets("Print").Select Range("A5").Select End Sub In the <Criteria Range I enter the date in cell E1. When I click the command button to activate the code, I want it to filter all the dates in column A to come up with the date entered in cell E1 as the criteria and copy the corresponding cells. Unfortunately, it's not filtering. It only copies ALL the date in cells A5:J500 and copies them to Print!A5. Does anyone have an idea what's wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still having Filter/Copy problems
Assume the column in your Data Table that contains the date has the header
Due so to follow the instructions I gave you E1: Due E2 7/15/2007 -- Regards, Tom Ogilvy "Mark" wrote: Tom answered my original post titled, "Filter and Copy?", but I'm still having problems with it. My code to Filter and Copy data from one sheet and transfer it to another is: Sub Advanced_Filter() Range("A5:J500").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("E1:E2"), _ CopyToRange:=Range("Print!A5"), _ Unique:=True Sheets("Print").Select Range("A5").Select End Sub In the <Criteria Range I enter the date in cell E1. When I click the command button to activate the code, I want it to filter all the dates in column A to come up with the date entered in cell E1 as the criteria and copy the corresponding cells. Unfortunately, it's not filtering. It only copies ALL the date in cells A5:J500 and copies them to Print!A5. Does anyone have an idea what's wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still having Filter/Copy problems
OK. I set it up that way and it works OK. Now, the problem I have it is
doesn't copy ALL the dates. Example, there are 25 entries on 8/5/07 and it only filters/copies 2. Thanks for your help so far. I misunderstood your previous instructions. Mark "Tom Ogilvy" wrote in message ... Assume the column in your Data Table that contains the date has the header Due so to follow the instructions I gave you E1: Due E2 7/15/2007 -- Regards, Tom Ogilvy "Mark" wrote: Tom answered my original post titled, "Filter and Copy?", but I'm still having problems with it. My code to Filter and Copy data from one sheet and transfer it to another is: Sub Advanced_Filter() Range("A5:J500").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("E1:E2"), _ CopyToRange:=Range("Print!A5"), _ Unique:=True Sheets("Print").Select Range("A5").Select End Sub In the <Criteria Range I enter the date in cell E1. When I click the command button to activate the code, I want it to filter all the dates in column A to come up with the date entered in cell E1 as the criteria and copy the corresponding cells. Unfortunately, it's not filtering. It only copies ALL the date in cells A5:J500 and copies them to Print!A5. Does anyone have an idea what's wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still having Filter/Copy problems
do some of your dates contain Time Values?
If so, can you make them dates. Select the column and do Format=General and see what is stored there For 8/5/2007 you should see the date serial: ? CDBL(DateValue("8/5/07")) 39299 If it show 39299.25, that represents 6AM on 8/5/07 and wouldn't meet your criterial. If that is your problem then clean up the data or use criteria like E F Due Due ="=8/7/2007" ="<8/8/2007" and change your criteria to E1:F2 -- Regards, Tom Ogilvy "Mark" wrote: OK. I set it up that way and it works OK. Now, the problem I have it is doesn't copy ALL the dates. Example, there are 25 entries on 8/5/07 and it only filters/copies 2. Thanks for your help so far. I misunderstood your previous instructions. Mark "Tom Ogilvy" wrote in message ... Assume the column in your Data Table that contains the date has the header Due so to follow the instructions I gave you E1: Due E2 7/15/2007 -- Regards, Tom Ogilvy "Mark" wrote: Tom answered my original post titled, "Filter and Copy?", but I'm still having problems with it. My code to Filter and Copy data from one sheet and transfer it to another is: Sub Advanced_Filter() Range("A5:J500").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("E1:E2"), _ CopyToRange:=Range("Print!A5"), _ Unique:=True Sheets("Print").Select Range("A5").Select End Sub In the <Criteria Range I enter the date in cell E1. When I click the command button to activate the code, I want it to filter all the dates in column A to come up with the date entered in cell E1 as the criteria and copy the corresponding cells. Unfortunately, it's not filtering. It only copies ALL the date in cells A5:J500 and copies them to Print!A5. Does anyone have an idea what's wrong? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still having Filter/Copy problems
I think you're some kind of genius, or something.
Thanks Tom! My code for the autodate was =Now instead of =Date. Makes a world of difference. Your help is much appreciated. Mark "Tom Ogilvy" wrote in message ... do some of your dates contain Time Values? If so, can you make them dates. Select the column and do Format=General and see what is stored there For 8/5/2007 you should see the date serial: ? CDBL(DateValue("8/5/07")) 39299 If it show 39299.25, that represents 6AM on 8/5/07 and wouldn't meet your criterial. If that is your problem then clean up the data or use criteria like E F Due Due ="=8/7/2007" ="<8/8/2007" and change your criteria to E1:F2 -- Regards, Tom Ogilvy "Mark" wrote: OK. I set it up that way and it works OK. Now, the problem I have it is doesn't copy ALL the dates. Example, there are 25 entries on 8/5/07 and it only filters/copies 2. Thanks for your help so far. I misunderstood your previous instructions. Mark "Tom Ogilvy" wrote in message ... Assume the column in your Data Table that contains the date has the header Due so to follow the instructions I gave you E1: Due E2 7/15/2007 -- Regards, Tom Ogilvy "Mark" wrote: Tom answered my original post titled, "Filter and Copy?", but I'm still having problems with it. My code to Filter and Copy data from one sheet and transfer it to another is: Sub Advanced_Filter() Range("A5:J500").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("E1:E2"), _ CopyToRange:=Range("Print!A5"), _ Unique:=True Sheets("Print").Select Range("A5").Select End Sub In the <Criteria Range I enter the date in cell E1. When I click the command button to activate the code, I want it to filter all the dates in column A to come up with the date entered in cell E1 as the criteria and copy the corresponding cells. Unfortunately, it's not filtering. It only copies ALL the date in cells A5:J500 and copies them to Print!A5. Does anyone have an idea what's wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy only visible cells after filter is applied/ sum after filter | Excel Worksheet Functions | |||
Filter problems | Excel Programming | |||
Filter problems | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming |