Macro/Formula to Combine Two Spreadsheets -2

[tgudex]

Hi,

I am attempting to put the last figure in a group (variable length) located in column G (per account per month) in 'Test(1)' and have the same figure appear in the appropriate cell in 'Master(1)'. For example, I would like the figure in cell G32 within the 'Test(1)' worksheet ($846,839,389.87) to be automatically copied into cell ER2 within the 'Master(1)' worksheet. Then the contents of G65 copied to ER3, and so on. The data in 'Test(1)' is a just a sample, I have 10 spreadsheets that I will need to map to one Master worksheet.


Please let me know if you can assist.

Thank you for your help in advance.

-Tim

[AB33]

Tim,
since no one has come to your rescue yet, I thought I will have a go.

This code works, but there is big "IF". The code can only work if your data structure will not change.For.e.g you have the word "Tot in column "J". I have included a colour index to check if the code is picking up the right row. It is loop code and so could potentially be slow. Well, it is my two cents and hopefull others may be come up with a better code.

Please Login or Register  to view this content.

[tgudex]

Hi AB33,

Thanks much for your reply. I have posted the code above into my Master(1) spreadsheet and ran the macro. I immediatly received a message stating "Subscript Out of Range" and no data appeared in Master(1). I did see cell J32 highlight in red while no other cells were highlighted.

Any thoughts?

Thanks,

Tim

[AB33]

Did you run my code? Please see attached.
I assumed your master sheet is "master"
change this line of the code

Please Login or Register  to view this content.

in to

Please Login or Register  to view this content.

[tgudex]

Hi AB33,

I did look at your attached documents and this looks like a good start. However I do not see the corresponding account numbers in column A. Is it possible to have these linked? Please a thread below that another individual helped me with. This thread is what I would like to duplicate but using the last figure in column G rather than the sum of column I. Any ideas?

http://www.excelforum.com/excel-prog...html?p=3008416

Thanks,

Tim

[AB33]

Try this one. Please note some of your data are blank. Please include Accounts No
in Master A2

Please Login or Register  to view this content.

[jaslake]

Hi Tim

Here's a variation of the previous code. Let me know of issues.

Please Login or Register  to view this content.

[tgudex]

Hi John,

Worked perfectly. Thanks much!

Regards,

Tim

[jaslake]

You're welcome...glad I could help.