How do I summarize several different worksheets into one single Table.

M

Maclear

Hey there,

I'm pretty sure that the subject must have been covered already mor
than once. But I can't seem to get the information I require
Therefore, I humbly ask of whoever has the skill to enligthen me
Thanks in advance.

Here goes.

I have to create a Workbook, each Worsheet of that workbook will hav
the same structure and will list the characteristic of a compan
vehicule. One Vehicule per Worksheet. The data in those worksheet is t
be arranged in columns.

Now the tricky part: all the data of those 60 different vehicule
spread on 60 worksheet will have to be summarized in One single Tabl
(on the very first worksheet). In this summary the data will have to b
arranged in lines.

There is one constraint: It should be possible to add a new vehicul
worksheet and have it fed into the summarizing table automatically.

I did something quite similar using VB a few years ago, but th
thing was messy and I am not a programmer so I'm looking maybe for
builtin function that could make things easier for me.

Anyway I will be eternally grateful to whoever will share insights
comments, criticism, or link to a full-scaled tutorial with me.

l:) :
 
J

Jim Cone

Maclear,

The following code goes in the "ThisWorkbook" module in your workbook.

It uses the "event" that occurs when a new sheet is added to a workbook.
"Sh" is the new sheet object.
strName is the variable that holds the name of the new sheet.
lngCell is the variable that holds the row number of the blank cell below the last entry in Column B.

In this example, four adjacent cells in the blank row have a formula entered
into them that reads four cells from Column D on the new sheet.

Test this by naming a sheet "SheetSummary", filling in a few cells in Column B, adding a sheet and checking the formulas that are
entered.
Hope this can get you started...

'--------------------------------------------------------------
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim strName As String
Dim lngCell As Long

strName = Sh.Name
With Worksheets("SheetSummary")
lngCell = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
.Cells(lngCell, 2).Formula = "=" & strName & "!$D$5"
.Cells(lngCell, 3).Formula = "=" & strName & "!$D$7"
.Cells(lngCell, 4).Formula = "=" & strName & "!$D$9"
.Cells(lngCell, 5).Formula = "=" & strName & "!$D$11"
End With
End Sub
'--------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA
 
M

Maclear

Ok, it looks great and I think I might end up doing exactly what I
planned to.

But i need a little detail on this line:

lngCell = .Cells(.Rows.Count, 2).End(xlUp).Row + 1

What Does it do?
 
Top