How to create a Summary tab off multiple tabs with similar data

[exalex]

How can I create a summary tab using multiple tabs with similar data? I have laid out 3 tabs below, "John", "Andrew", and "summary". I would like the summary tab to look up off of over 50 different tabs. See below, thank you in advance!

[TABLE="width: 583"]
<tbody>[TR]
[TD="colspan: 2"]Tab Name: John
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]John
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="colspan: 3"]Birth Date
[/TD]
[TD]1/1/2005
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="colspan: 3"]Favorite Food
[/TD]
[TD]Pizza
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="colspan: 3"]Sport
[/TD]
[TD]Soccer
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Tab Name: Andrew
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Andrew
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="colspan: 3"]Birth Date
[/TD]
[TD]12/6/2004
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="colspan: 3"]Pet
[/TD]
[TD]Dog
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="colspan: 3"]Favorite Food
[/TD]
[TD]BBQ
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="colspan: 3"]Sport
[/TD]
[TD]Tennis
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Summary Tab
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Birth Date
[/TD]
[TD]Pet
[/TD]
[TD]Favorite food
[/TD]
[TD]Sport
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]John
[/TD]
[TD]1/1/2005
[/TD]
[TD][/TD]
[TD]Pizza
[/TD]
[TD]Soccer
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Andrew
[/TD]
[TD]12/6/2004
[/TD]
[TD]Dog
[/TD]
[TD]BBQ
[/TD]
[TD]Tennis
[/TD]
[/TR]
</tbody>[/TABLE]

 

[exalex]

thank you in advance

 

[gvillepa]

How about just create a summary tab and reference each sheet?

Summary Tab:

Cell A2: = John!A2
Cell B2: = John!B2
Cell C2: = John!C2

Cell A3: = Andrew!A3
Cell B3: = Andrew!B3
Cell C3: = Andrew!C3

 

[exalex]

thank you - I was hoping to find a quicker way as there are 50 different tabs and 50 different names - would be a lot of legwork in my opinion to link each tab by itself.

 

[My Aswer Is This]

Why is row (2) empty?

 

[exalex]

no reason, just spacer row.

 

[My Aswer Is This]

It just makes things like your asking for harder. And like why does name go in column "B" but all other data goes in column "E"

Laying out how you do things can be important when some day you want to do things like your asking for.

 

[gvillepa]

vba is not my expertise, but you could loop all worksheets to pull range and copy it to target sheet "Summary".

https://msdn.microsoft.com/en-us/library/cc793964(v=office.12).aspx

 

[exalex]

Thank you. Is there a quick way to reference a tab name? Instead of finding and replacing on a vlookup over 50 times?

 

[My Aswer Is This]

Sure a loop would work.

But there are several problems here:

For example John does not have a Pet so there is no row for Pet
But Andrew does have a Pet so there is a row designated for pet.