Macro to copy the filter data from excel file and copy to other excel file?

[bunty]

Hi,

My requirement is I have one excel file where I can filter the data based on time or some other column.I have to copy some of the column from that sheet and paste to other excel file.
As I don't have much idea of writing vba code.Please help me to write the code or if you have share with me.

Thanks....

[farrukh]

bunty,

Please attached the sample file what you need and what would be the results?

cheers
farrukh

[bunty]

Hi Farrukh,

First of all thanks for your response.

I have 2 excel file:
1. test_file: which contain all the data.

2. test_copy_file:where I need to move data based on the filter condition.This file contain two sheets like open and update.

My requirements are:

1. I have to filter data based on open_date column with single date selection (e.g: 3/2/2012) and copy these data to test_copy_file under open sheet.

2. I have to filter data based on open_date column with multiple range selection (e.g: from 12/12/2011 to 3/2/2012 ) and under status column ( open and update).Paste these data to update sheet under test_copy_file.

Thanks,
Bunty

bunty,

Please attached the sample file what you need and what would be the results?

cheers
farrukh

[farrukh]

Hi Bunty

See the attached file as you desired.

* Click on the button "Call desired excel sheet" and search your desired excel file which is test_copy_file.xls when select a file it auto copy the data from test_copy_file to attached F_works.xlsm after that a popup calander will apear you need to give starting date and ending date as you desired then you find three buttons which are open,update and close , when you click to open your filter data by dates will be in open sheet when you click to update button your data on date filter will be on sheet update and same for close.


Thanks
Farrukh

If you satisify from answer please mark as a solved please

[bunty]

Hi Farrukh,

Thank you so much for your support.

It’s extremely good. I never thought to do like you have done.But my requirement is little different.

1. Based on the "Call desired excel sheet" button I am not able to select any excel file.

2.one of my command button like update will filter based on date range (12/11/2011 to 02/02/2012) with two status option will be selected (open and
Update both).

3. Other two button like (open or close) will filter only on single date selection (e.g.: 22/02/2012).

4. I don't want to copy whole filter data to target location.example:
source sheet target sheet
column A column A
column B (no need to copy)
column C column F
so on....

Thanks..again for your time and help.

[farrukh]

Hi Bunty,

1. If you are not able to select any file from search by clicking on button. I suspect that you want to select .xlsx file instead of .xls for selecting the all excel file on search you need to change the Filter name code line.

[VBA]filter = "Text files (*.xls), *.xls"[/VBA]

TO

[VBA]
filter = "Text files (*.xl*), *.xl*"
[/VBA]

When the popup calander will apear you can pass the starting and ending date then click one by one on open,close and update.


Hope this work for you :-)

Thanks you
farrukh

[bunty]

Hi Farrukh,

Sorry, when I changed to filter = "Text files (*.xl*), *.xl*", I am getting error compile error called “cannot find project or library “.

I understand that but the requirement is not exactly its working right now.
It should be like that:

For update: I am selecting two date rate range, which is perfect here. But with that I want to select status filter also. Status column contain 3 things open, close and update. It should select open and update both.

E.g: during filter I can select date range that is to and from and status column open and update both selected.So, when I filter status column contain open and update data.
02/12/2010 to 23/02/2012 and open + update (from status column)

For open: I want to pass only one date either it’s a current date or yesterday date.
E.g: 23/02/2012
Thanks... for your quick response...

Hi Bunty,

1. If you are not able to select any file from search by clicking on button. I suspect that you want to select .xlsx file instead of .xls for selecting the all excel file on search you need to change the Filter name code line.

[vba]filter = "Text files (*.xls), *.xls"[/vba]

TO

[vba]
filter = "Text files (*.xl*), *.xl*"
[/vba]

When the popup calander will apear you can pass the starting and ending date then click one by one on open,close and update.


Hope this work for you :-)

Thanks you
farrukh