(URGENT) Search for matching data, copy and pasting from a closed excel file

[dani_n88]

Hi Everybody,

I have three separate excel files: a closed Interest spreadsheet, a closed Balance spreadsheet, and an open Database spreadsheet. The two closed excel files change every quarter and I want the updated data be copied into the Database file. I need a code/macro in “Database.xlsm” that on a click of a button once every quarter, it would search for the matching “ID” in the other two closed excel files, copy the “Interest” and “Balance” data for that “ID” and paste it in the “Interest” and “Balance” fields for the relevant quarter in the “Database.xlsm”. I wouldn’t want the next quarter information overwrite the previous quarter’s data and would want to keep the old data. This is a tough one for me and I would really appreciate it if you guys could help me with this.

[dani_n88]

So After playing around with it a bit more, I figured VLOOKUP does exactly what I need. However I'd still need a Macro that would 1) command the VLOOKUP to execute/refresh (for example if data in the other two spreadsheet change, I'd want to hit refresh and it would fetch the new data) and 2) repeat the action for next quarter when data becomes available with a new file name (e.g. Q1_Interest.xlsx becomes Q2_Interest....)

[arlu1201]

You said

I wouldn’t want the next quarter information overwrite the previous quarter’s data and would want to keep the old data.

But you also say that when numbers change, you want to fetch the new data.

Hmmm, so if you are going to do this via a macro, you need to decide which of the options you want - to retain old data or remove old data. Because, each time you trigger the macro, the option that is coded will be performed.

Or, you will need 2 separate macros - 1 to overwrite existing data and the other one to retain existing data. So if its a fresh data set, you can choose which macro you want to run accordingly.

[dani_n88]

By that I meant to say, if I openned Database.xlsm and I clicked on the Fetch button on top of the (First Quarter) column to get the First Quarter Data, I would want them to be saved in Database.xlsm under that column, and then when I reopen the Database.xlsm file next quarter to click on the Fetch button on top of the (Second Quarter) column it would fetch the data from the new files 2Q12_Interest and 2Q12_Balance and save them in the (Second Quarter) Column without touching the (First Quarter) Column. and it would go on the same way for the upcoming quarters. I would have to do this every quarter for the next 7 years!

I'd have to automate this process for the next 28 quarters. The only thing that would change every quarter is the name of the files and the column data would be pasted in (e.g. 1Q12_Interest for first quarter, then 2Q12_Interest for second quarter, 3Q12_Interest....and so on).

[dani_n88]

I have made some edits and have attached the files here:

1Q12_Balance.xlsx
1Q12_Interest.xlsx
Database.xlsm

SO this is the Macro I have for now in Database.xlsm first Fetch button:

Please Login or Register  to view this content.

This is technicially what I need, and it works, but I need three additional modifications.

1) Instead of giving it a terminal row number to loop thru, I'd want it to start the search from the first "ID" in Database.xlsm and continue looping until the last "ID" in the Database.xlsm. Because I might later on add new "ID"s to the list there and don't want to change the code everytime to include the new row number.

2) Instead of defining the Array Ranges in VLOOKUP, I'd need it two look thru the first to last row existing for my specific columns in the other two source files, because the number of rows might change in them as new data would be added.

3) I'd still need to figure out a way to have this code read dynamic filenames as the next quarter filenames will change (1Q12_Interest becomes 2Q12_Interest and same with Balance. I want a Fetch button on top of every Quarter's column and have it do the same thing the First Quarter's Fetch button does except do the Vlookup in the new filenames for that specific quarter.

Thanks

[jaslake]

Hi dani_n88

Assign the same Macro to each button...let me know of issues.

Please Login or Register  to view this content.

[dani_n88]

Hi John,

That code is genius. I tested all you variables and they return a value. everything seems to work except there's a problem with the VLOOKUP function. I keep getting the error:

Runtime error '1004':
Unable to get the VLOOKUP Property of the WorksheetFunction class

and Debug highlights these codes:

Please Login or Register  to view this content.

Do you know what it could be?

[jaslake]

Hi dani_n88

I don't know. I'll attach my Test Files and let you play with them to see if the code works for you on the Test Files (it does for me).

These files Qtr Files.jpg should be in folder C:\Excel Program. There appears to be a limit on the number of files one can attach so you'll need to make copies of 1Q12 and rename them to 2Q12, 3Q12 and 4Q12 to run the test.

The File Database V1.xlsm can be wherever you wish.

Let me know your results with the Test Files.

[dani_n88]

John thank you very much. Your files work great. This can't get any better. Issue resolved.

Would you just briefly tell how you defined the fuction of the buttons? Looks like buttons have to be created in the first row of each columns to be operational. I would have to recreate the same macro for another spreadsheet with real long data and I'd need to know where to define the action of the buttons. Do I just recreate the RobbonX_Code module and add in ShowATPDialog and GetATPLabel?

If so what is the purpose of "fDialog", "RES" and the range A10 you have given it? Do I create the "RES" sheet?

[jaslake]

Hi dani_n88

Your Buttons can be just about anywhere you wish; the code finds it's address and the the code tells it where to look relative to that address. In this instance, we've told the code to look just below the cell where the clicked Button lives to find the File Name to open.

Please Login or Register  to view this content.

Regarding this

Do I just recreate the RobbonX_Code module and add in ShowATPDialog and GetATPLabel?

and this

If so what is the purpose of "fDialog", "RES" and the range A10 you have given it? Do I create the "RES" sheet?

None of this applies to the code I provided; it must reference some other code that's not in the sample files.