Consolidating data from mulitple worksheets

[jrschaef]

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!!

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

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