Lookup Values in different sheets

[Les Stout]

Hi all, I have a workbook that has a variable amount of worksheets, each
named with a date. These worksheets contain downloads of all all parts
delivered by part number, there can be more than one entry.
The first sheet is a summary sheet. In this sheet i have the part
numbers in column "A" that i need to investigate, I need to scan through
sheet 1 for the each of the numbers in column A and add up the parts
deliverd and place the total in column "C" next to the relevant part
number.
I would also like to use the sheet name as the column header. It do
this for all the part numbers in the Summary sheet in column "A". Then i
would like to go to the next sheet and put the totals in column "D".
I have tried to piece together some code but just get horribly lost, any
help would be greatly appreciated.

Les Stout

 

[Bob Phillips]

Les,

Try this code

Sub CreateSummary()
Dim sh As Worksheet
Dim iLastRow As Long
Dim i As Long
Dim iCol As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iCol = 3 'column C
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
Cells(1, iCol).Value = sh.Name
Cells(2, iCol).Formula = "=SUMIF('" & sh.Name & "'!A:A,A2,'" &
sh.Name & "'!B:B)"
Cells(2, iCol).AutoFill Cells(2, iCol).Resize(iLastRow - 1)
iCol = iCol + 1
End If
Next sh

End Sub

You will need to adjust the A:A and B:B to the actual columns on the other
sheets.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Les Stout]

Hi Bob, tried it and the headings are great but just get Zero's ?? Have
changed the the A to C were the other sheet part numbers are and the H
columns have the number of deliveries that need to be added per part
number.

Sub CreateSummary()
Dim sh As Worksheet
Dim iLastRow As Long
Dim i As Long
Dim iCol As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iCol = 3 'column C
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
Cells(1, iCol).Value = sh.Name
Cells(2, iCol).Formula = "=SUMIF('" & sh.Name & "'!C:C,C3,'" & sh.Name &
"'!H:H)"'<== In C is the equivelent No.& in H is the value that needs to
be added.
Cells(2, iCol).AutoFill Cells(2, iCol).Resize(iLastRow - 1)
iCol = iCol + 1
End If
Next sh

End Sub


Les Stout

 

[Bob Phillips]

Les,

In my submission, I assumed that the part numbers were in column A in the
summary and data sheets, and the values in column B. There are a couple of
places this is used

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

this is getting the last row on the Summary sheet, assuming the part ids are
in column A. It might need changing.

Cells(2, iCol).Formula = "=SUMIF('" & sh.Name & "'!A:A,A2,'" & sh.Name &
"'!B:B)"

this is seeting up a formula to look in column A in the data sheets for the
part number in A2 of the summary sheet, and getting the numbers in column B
of the data sheets. You have changed to C:C and H:H as this is presumably
where the part ids and values on the data sheets are, but you also changed
the A2 to C3. That part should be the cell reference of the first part
number on the summary sheet. If it is really line 3, then Cells(2,
iCol).Formula should also be changed to Cells(3, iCol).Formula, etc. and
this line

Cells(2, iCol).AutoFill Cells(2, iCol).Resize(iLastRow - 1)

should reflect the lines before the first data line, i.e.

Cells(2, iCol).AutoFill Cells(2, iCol).Resize(iLastRow - 2)

or

Cells(2, iCol).AutoFill Cells(2, iCol).Resize(iLastRow - 3)

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Les Stout]

Thanks so much Bob, found it out for myself before your answer came
through.

Thanks again and have a great easter.

best regards,

Les Stout