PDA

View Full Version : filter data and than load it into master file



coolin
06-14-2012, 11:32 PM
Hello people,

I have a master xls file that needs to be updated monthly with data from source file.

I want to create a button on the master file with a macro attached that will check the headers from the source file to have the same value and order like the headers in the master file, in case of differences a msg box must will say that are differances between the 2 headers, and the macro will stop. I have to do this checking part because the header of the source file can be changed (order of the columns or titles.)
In case there are no differences between the 2 files, I want the source file to do a filter on "Deal Description" column for values that contain "MDP", copy and paste data in the master file on the next available blank row, than do another filter on "Pre-Sales ID" column for values that contain "DDP", copy and paste data in the master file on the next available blank row.

Note that the source file must be open before the macro will start, in case it's close a msg box should appear with a message to open the source file.

I have attached the structure of the master file were I have to add the button that will do the check and all filtering and copy and pasting.
If anyone have done something similar I will appreciate the help.

Thanks!

Tinbendr
06-15-2012, 06:33 AM
Welcome to the board!

If you've posted this elsewhere, please provide the link to that site as well.

Instead of telling us how you want it written, why don't you state the problem and let us help you resolve it.

It would help if we had a sample of the source file, too. Make three different sheets with different column headers as you describe.

What range of information do you want copied to the Master? Any row filtered gets copied to the Master? Same with both filters?

coolin
06-15-2012, 08:11 AM
Welcome to the board!

If you've posted this elsewhere, please provide the link to that site as well.

Instead of telling us how you want it written, why don't you state the problem and let us help you resolve it.

It would help if we had a sample of the source file, too. Make three different sheets with different column headers as you describe.

What range of information do you want copied to the Master? Any row filtered gets copied to the Master? Same with both filters?
Hello Tinbendr
well the problem it's that I'm new to VBA and will help if someone had done a similar macro, so I can have starting point.

The source file has same headers, in the master file we gather information from one month to another..
From source I need to copy all the data (without header):
- apply 1st filter on source file, copy paste to master file on the next blank row
-apply 2nd filter on source file, copy paste to master file on the next blank row

Thanks for your interest

lotuxel
06-18-2012, 02:14 AM
I have the file want to share for this subject.
I don't know how to upload to here and if some one interesting pls email to me. lotuxel@gmail.com

Tinbendr
06-18-2012, 05:01 AM
Right here

lotuxel
06-18-2012, 07:44 PM
:hi:
Dear Coolin,
This is the my first time to share my creation and hope enjoys.
forgive my poor english and may have some bugs.
I have many sources learned from this vba express forum and distributes to other begining user.


This file I created just on the way, but it function properly.
Firstly, u may be difficult to use it even the manual show as below:-
May be my English is poor and can not explain properly.
anyway..
1). the system will displays all the workbooks you opened in your compter.
if your target to open just click the "open file" to open it.
2).choose the worksheet that your want to take out from (Workbook from frame)
3).then choose the range [Source cell (1st cells)] that use as query (criteria) = (Sources)
(Note! default last row = end(xlup).row)
4). choose the range [Target column to extract] then click the "Add column" to add your choices.
4.1. you can choose more than one columns even not continues columns. (just use 4. again and click the "Add column" agian to append columns..

5). choose the workbook that you need to fill or places.
6). choose the worksheet that you need to fill or places.
7). then choose the range [Source cell ] that use as query (criteria) = (Sources)
(Note! default last row = end(xlup).row but you can choose last row of continues cells)
8). choose the range [Target column to place (accept)] then click the "Add column" to add your choices.
8.1. you can more than one columns even not continues columns. (just use point8. again and click the "Add column" agian to append columns.
(Note! must be matched columns quantity with point4)

9). you can save the template before run or Go.

Please advice and free to ask me any queries.
Hi admin,
Please suggest and advice where need to inplement etc..

thks
lotuxel