Consolidating data from mulitple worksheets

jrschaef

New Member
Joined
May 29, 2003
Messages
12
I am trying to consolidate data from multiple worksheets based on the name of the tab. For example, I have 200+ tabs all with the name format of "Dept - Location". I have them grouped together by Department first, so I have 1010 - Orlando then 1010 - Miami, etc. and have created a Dept 1010 summary. But I also want to create a summary by location and my tabs are not ordered by location. Can I create some sort of formula that looks at the tab name to say If tab name = Miami, then add A3 from that sheet and then have that go through all of the sheets to calculate a total for the Miami location?

One extra piece of information....My tabs are all in the same format with account number down the left and month across the top.

Thanks!!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt F11)

Code:
Sub ddd()
For a = 1 To Sheets.Count
b = Worksheets(a).Name
If Right(b, Len(b) - InStr(b, "-")) = "miami" Then
Cells(a, 1) = b
Cells(3, 2) = Cells(3, 2) + Worksheets(a).Cells(3, 1)
End If
Next a
End Sub
Run the macro. It will list sheets with miami in it and totals A3 and puts it in B3. If that works for you, we can improve over to cover other locations and other data.
Ravi
 

Forum statistics

Threads
1,181,730
Messages
5,931,701
Members
436,799
Latest member
BasOo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top