| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting text within an Entire Workbook
Thank you
JLatham wrote: 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] 4563 3434 Missing -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/201004/1 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Name an Entire Workbook | Excel Discussion (Misc queries) | |||
| Name an Entire Workbook | Excel Discussion (Misc queries) | |||
| Print Entire Workbook | Excel Discussion (Misc queries) | |||
| count text strings in an entire workbook | Excel Worksheet Functions | |||
| Search Entire Workbook | Excel Discussion (Misc queries) | |||
Threaded Mode
