Macro/Formula to Combine Two Spreadsheets

**tgudex** · 10-25-2012, 04:47 PM

Hi,

I am attempting to put the sum of a group (variable length) located in column I (per account per month) in 'Test' and have the same figure appear in the appropriate sale in 'Master Performance Test'. For example, I would like the figure in cell I32 within the 'Test' worksheet (.046112) to be automatically copied into cell EF4 within the 'Master Performance Test' worksheet. Then the contents of I66 copied to EF5, and so on.

Master Performance Test.xls

Test.xls

Please let me know if you can assist.

Thank you for your help in advance.

-Tim

**jaslake** · 10-25-2012, 06:28 PM

Hi tgudex

In your actual environment:

Are these two separate workbooks or two worksheets in the same workbook?
Is there no Header Row in Test.xls?
Is Column A of Master Performance Test pre-populated with Accounts or does it need populated based on Column C of Test.xls?

**tgudex** · 10-26-2012, 09:07 AM

Hi jaslake,

1) The Test.xls file I uploaded is just a sample of a larger worksheet. Since I have an old version of Excel, I have a workbook of approximately 9 worksheets that contain data for about 150 accounts for every month since 2000. To answer your specific question, I can either move the MasterPerformanceTest.xls into the Test.xls workbook or keep them separate, makes no difference to me. What do you believe is easier?

2) I can put a header row in Test.xls if necessary, again, makes no difference to me.

3) Column A is not pre-populated with accounts and will need to be populated based on Column C if that is possible.

Thank you much. I appreciate your help.

-Tim

**jaslake** · 10-26-2012, 09:18 AM

Hi tgudex
Are all the accounts the SAME in all approximately 9 worksheets or could there be some accounts that exist in one worksheet and not in another?

**tgudex** · 10-26-2012, 09:28 AM

Hi jaslake,

Each worksheet may contain different accounts, and each account may not have data for all months/years. There will be no account number greater than 9999, but there is not data for every account integer 1-9999. Is it possible to have accounts 1-9999 in the MasterPerformanceTest.xls worksheet, fill the data, then go back and delete the rows that do not contain data?

Thanks,

Tim

**jaslake** · 10-26-2012, 01:06 PM

Hi Tim

Try the code in the attached. It appears to do as you describe. Let me know of issues.

Run the code from the Button on Master.

**tgudex** · 10-29-2012, 05:44 PM

Hi John,

Sorry for the late response. This is exactly what I needed, now I just need to apply it my full spreadsheet. I hope to do this tomorrow and will get back to you with my results.

Thanks again for your help.

-Tim

**tgudex** · 11-14-2012, 11:57 AM

Hi John,

I applied the macro to my full workbook and it worked beautifully! Thanks again. I now have a similar problem that I have been unable to solve. Essentially I need to map the last figure in column G for each month for each account from the Test 1 tab to a separate spreadsheet similar to "Master"

For example, the figure in cell G31, $846,839,389.87 in the "Test 1" spreadsheet would need to map to the corresponding tab in the "Master" spreadsheet.

I have already run the macro on the whole workbook so all my spreadsheets look similar to "Test 1".

Do you have any thoughts? Thank you in advance.

Regards,

Tim

**jaslake** · 11-14-2012, 07:39 PM

Hi Tim

Please start a new Thread for this new issue. You won't get too many looks posting a question in a SOLVED Thread. Explain your issue and, if you wish, PM a link to your new Thread.