Automating a manual exercise

[ciaran.hudson]

Hi,

I have an excel workbook containing about 80 tabs.
All tabs contain data in the same format.
Some tabs contain quantities, some do not.
If a tab contains quantities, they are in columns J and K.

What is the quickest way to get a list of all tabs and the total
quantity of each?

At present I am manually creating a list of all tabs on a separate tab
and doing a sum function which references each individual tab, again
manually.

Regards,
Ciarán

 

[MartinW]

Hi Ciaran,

I don't know about listing the tabs but this will sum the cells for you.

Insert a new sheet and call it First and place it at the start of your tabs.
Insert another sheet and call it Last and place it at the end of your tabs.

Then in your totals tab put this formula in J5
=SUM(First:Last!J5)
This will sum J5 in every sheet between First and Last.

Your totals tab should be out of the range,
i.e. to the right of the Last tab.

HTH
Martin


Hi,

I have an excel workbook containing about 80 tabs.
All tabs contain data in the same format.
Some tabs contain quantities, some do not.
If a tab contains quantities, they are in columns J and K.

What is the quickest way to get a list of all tabs and the total
quantity of each?

At present I am manually creating a list of all tabs on a separate tab
and doing a sum function which references each individual tab, again
manually.

Regards,
Ciarán

 

[Don Guillett]

Are you saying that the final result should be
name of sheet col J total col k total
a 7 1
b 0 4
c 9 0


--
D on Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi,

I have an excel workbook containing about 80 tabs.
All tabs contain data in the same format.
Some tabs contain quantities, some do not.
If a tab contains quantities, they are in columns J and K.

What is the quickest way to get a list of all tabs and the total
quantity of each?

At present I am manually creating a list of all tabs on a separate tab
and doing a sum function which references each individual tab, again
manually.

Regards,
Ciarán

 

[Don Guillett]

if so,
Sub totaltabs()
For i = 2 To Sheets.Count
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
With Sheets(i)
Cells(lr, 1) = .Name
Cells(lr, 2) = Application.Sum(.Columns("j"))
Cells(lr, 3) = Application.Sum(.Columns("k"))

End With
Next i
End Sub

 

[ciaran.hudson]

Thank you so much Don, that has done exactly what I want

 

[Don Guillett]

Glad to help. In the future, Pls try to give a more full explanation with
examples.