On the offchance that you could use another workbook with code in it to
perform the work, I wrote this up for you. Just start with a brand new
workbook in Excel. Press [Alt]+[F11] to enter the VB Editor and choose
Insert-->Module. Copy the code below into it and edit the Const values that
define the sheets and layout of the workbook to have the summary in it.
After that all you do is open this workbook and [Run] the Macro. You could
even put a button from the Forms toolbar or a text box from the Drawing
toolbar on a sheet and assign the macro to it so that when you click it, it
begins to work. It will prompt you to browse for the file to process and
once you choose that workbook, it just does it's thing! At the end, it
closes and saves that other workbook with the changes it made in it, and lets
you know it has finished.
Sub CreateSummary()
'these need to be changed to agree with the
'setup of all sheets except the summary sheet
'assumes all sheets other than the summary
'sheet are set up exactly alike
'
Const itemColumn = "A"
Const missingColumn = "B"
'change as needed, but keep it in UPPERCASE
Const missingTerm = "MISSING"
'and these need to be changed to match
'the name and setup on your summary sheet
'in that other workbook
'name of the summary sheet
Const summaryName = "SUMMARY"
'column you want the part # in
Const itemColumn = "A"
'next assumes row 1 has labels in it
'and we'll add labels later
Const firstSummaryRow = 2
'end of user definable values
Dim otherWBName As String
Dim otherWB As Workbook
Dim otherWS As Worksheet
Dim missingList As Range
Dim anyMissingEntry As Range
Dim offsetToItem As Integer
Dim summaryWS As Worksheet
Dim nextSumRow As Long
otherWBName = Application.GetOpenFilename
If Trim(UCase(otherWBName)) = "FALSE" Then
'user cancelled the operation
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open otherWBName, False, False
Set otherWB = ActiveWorkbook
Application.DisplayAlerts = True
ThisWorkbook.Activate
Set summaryWS = otherWB.Worksheets(summaryName)
'clear old data and set up labels for the new
summaryWS.Cells.ClearContents
summaryWS.Range(itemColumn & 1) = "Missing Items"
'if you don't want them, you can delete this
'section and the one later on similarly identified
'without affecting the overall functionality.
'bell and whistle
summaryWS.Range(itemColumn & 1).Offset(0, 1) = _
"On Sheet"
summaryWS.Range(itemColumn & 1).Offset(0, 2) = _
"At Row"
'end bell and whistle
offsetToItem = Range(itemColumn & 1).Column - _
Range(missingColumn & 1).Column
For Each otherWS In otherWB.Worksheets
If otherWS.Name <> summaryWS.Name Then
Set missingList = otherWS.Range(missingColumn & _
"1:" & otherWS.Range(missingColumn & _
Rows.Count).End(xlUp).Address)
For Each anyMissingEntry In missingList
If Not IsEmpty(anyMissingEntry) And _
UCase(Trim(anyMissingEntry)) = missingTerm Then
'is a missing item, report it
nextSumRow = summaryWS.Range(itemColumn & _
Rows.Count).End(xlUp).Offset(1, 0).Row
summaryWS.Range(itemColumn & nextSumRow) = _
anyMissingEntry
'bell and whistle again
summaryWS.Range(itemColumn & nextSumRow). _
Offset(0, 1) = otherWS.Name
summaryWS.Range(itemColumn & nextSumRow). _
Offset(0, 2) = anyMissingEntry.Row
'end bell and whistle
End If
Next
End If
Next
'all done now, do some housekeeping
Set missingList = Nothing
Set otherWS = Nothing
Set summaryWS = Nothing
Application.DisplayAlerts = False
'close the other workbook, saving the changes
otherWB.Close True
Application.DisplayAlerts = True
MsgBox "Missing Item Summary Completed.", vbOKOnly, "Task Completed"
End Sub
Yes Darn there are a lot of sheets and I am trying to get away from manual
labor. The sheets are continuously changed and I want the summary sheet to
[quoted text clipped - 19 lines]
