Match cell value from 1st WB with sheet name in 2nd WB, select cell and paste in first WB

[Tona]

I have two workbooks. In workbook "Number 1" I have an active sheet named "XYZ" with a column A containing list of Names and column B which is as follows

A ....................... B
Names ......... Budgeted
Pamela ............... 3
Pamela ............... 2
Pamela ............... 4
Pamela ............... 5
Kim .................... 1
Kim .................... 4
Michaela ...............2
Michaela .............. 5
Michaela .............. 1
Michaela .............. 8
Michaela ............. 10

OPTIONAL 1st I need to insert row under each last same name and sum values:


A .....................B
Names ...............Budgeted
Pamela ...............3
Pamela ...............2
Pamela ...............4
Pamela ...............5
...........................14
Kim ....................1
Kim ....................4
...........................5
Michaela ...............2
Michaela ..............5
Michaela ..............1
Michaela ..............8
Michaela .............10
...........................26


In workbook "Number 2" I have tabs named by same names as in workbook "Number 1" (So tab named "Pamela", "Kim", "Michaela").

I'm looking for macro that will find-match name ("Pamela") on the active sheet in column A with corresponding tab name in workbook "Number 2" , copy value A1 (eg. 20) from tab name ("Pamela") and paste it in the (optional) inserted row or in the last row of each name :

A .....................B.................C
Names ...............Budgeted........Actual
Pamela ...............3
Pamela ...............2
Pamela ...............4
Pamela ...............5
...........................14.................20
Kim ....................1
Kim ....................4
...........................5....................3
Michaela ...............2
Michaela ..............5
Michaela ..............1
Michaela ..............8
Michaela .............10
...........................26..................30


I appreciate any effort and suggestions!

[JOHN H. DAVIS]

Welcome to the Forum!! Maybe:

Please Login or Register  to view this content.

[Tona]

Not maybe, but DONE!

I've "upgraded" specific name of the sheet "XYZ" to "ActiveSheet".

Possible to do it without opening workbook "Number 2"?

I appreciate your effort.
If you'll be able to I would kindly ask for explanation.

Thank you.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Here is the code with comments added for explanation.

Please Login or Register  to view this content.

[Tona]

Dear John, thank you for your answers.

Could you help me with updated problem (after practical test):

1. which part does specify from which cell (column A) it starts the loop for? I would need a loop from eg A6 on.

2. after thinking would be even better if it doesn't insert the row, but puts the "sum" of numerical range in the next column at the "bottom of each last name" and underlines row till column A.
....A......H....I
Pamela...2
Pamela...2
Pamela...2....6
Kim........1
Kim........1
Kim........1....3

(could be solved by =IF(A6<>A7,SUMIF($A$6:A6,A6,$H$6:H6),"") )

3. and the last part when it searches for the value in second workbook, to paste that value in the same row as "sum of numerical ranges" before.

I want to understand the logic behind, so I can play then with columns, rows, etc.

Thank you!

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[Tona]

1. If I want to sum numbers in column eg. "H", put result in column "I" and paste figures from WB2 into column "J", which figures I should change (apart from Columns("B"))?

Would appreciate if You would put a note next to each eg. (3,2).

Thank you John

[JOHN H. DAVIS]

Can you attach a sample of both workbooks, with book 1 showing a before and after sheet? That way I can tell what you're layout looks like. To attach a file click Go Advanced scroll down to the Manage Attachments button, browse to the required file, and then push the Upload button.

[Tona]

I have figured it out and learned to adjust, thank you!

Question is: if figures in my B column are formulas eg. =(D1+E1)*F1 macro doesn't read the number where it should sum "numrange".

Any suggestions?

Regards

[JOHN H. DAVIS]

If you could Post a sample with some dummy data, that would help out.

[Tona]

Please see attached files.

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[Tona]

John, thank you.

I've modified the code for my needs.

Now I came to next challenge.

(Still talking about the SAME workbook)

What if I have to activate same operation for different range and different book.

So at the same time (as I run this operation) it should open a third workbook, match it with different range (the key stays the same "Pamela, "Kim",..) - can also "use" inserted rows from before, and paste figures in required column.

Far from truth but to get the idea:

Please Login or Register  to view this content.

Hope it is clear enough to understand.

[JOHN H. DAVIS]

Tona:

Please read the Forum Rules about posting code. Refer too Rule No. 3.

http://www.excelforum.com/forum-rule...rum-rules.html

[Tona]

Sorry, missed that part.

Done.

I appreciate any ideas!

[JOHN H. DAVIS]

It operates on the active sheet. How might the ranges change?

[Tona]

Operates on the same sheet.

Logic is the same as the #1 macro, find correspondent sheet name (eg. Pamela) in the Book2, copy A1 and paste it in the corresponding cell in Activebook.
Now at the same time it should loop with same criteria, find correspondent sheet name (Pamela) in Book3, copy A1 and paste it in corresponding range in Activebook. Destination range would be fixed as it was in case number 1.

Sorry for bad explanation.

I would appreciate any ideas and suggestions.