I need to pull specific information from Excel Workbooks with multiple sheets in them. (Snapshot below). I imagine it would be easiest to pull each row then count the quantity from there. The main issue is there are multiple sections in a sheet. I don't care which language is used as long as I can get the info, preferably onto a new sheet at the end of the workbook, but doesn't have to be, I need to get the counts the ProServices need to put together their financial model.
Each section will always have a title. In the above example those are MEL and MEA. The title will be different but the formatting of that cell is always the same. What I need is the count of the main item (in this case is the 2nd row below title and is bold) from each section and then added together (I can always do the total quantity after the fact). If possible, I would like all the following info to be pulled and displayed on a new sheet.
[Sheet Name] [Title of Section] [Main Item] [Qty]
I can format the sheet how I want it to look unless there is a way to do that as well! Open for input on that one!
Please note, some sections might have multiple main items. However, they will always be bold and left indented and be in the 'Materials' portion.
The only assumption I made was that the indent for the listed materials was done with leading spaces. The code creates a new sheet renames the sheet, creates headers. The code then iterates through all cells in column a and fills an array accordingly, the first element is the name which is set first and changes when the next worksheet is selected. the second element contains the section name, which is set after a cell with the value "Materials" is detected, it is set with the
Offset(-1, 0) since the section name is always followed by a cell with the value "Materials". Element 3 and 4 are set when a cell is with a value starting with either "WS" or "EDU" is detected. The entire array is then copied to the first available row on the output sheet.
The range to loop through consists of several parts:
'The range of cells to iterate through Ws.Rang(first cell, last cell) 'First cell Ws.Cells(rowindex, columnindex) 'The rowindex is the number of the row the cell is on. The formula "=ROW()" 'in a cell on a worksheets shows the rowindex of that cell. 'The columnindex is the number of the column the cell is on, the formula "=COLUMN()" 'in a cell on a worksheet shows the columnindex of that cell. 'cell "A1" would be Ws.Cells(1, 1) 'Here is where it get tricky, to find the last cell Ws.Rows.Count 'This returns the last rownumber available in the sheet Ws.Cells(Ws.Rows.Count, 1) 'Refers to the last possible cell in the first column Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row 'This returns the rowindex of the first cell which contains data in first column looking 'from the bottom upwards. Ws.Cells(Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row, 1) 'This refers to the first cell looking from the bottom up, in the first column.
For Each Cell In Ws.Range(Ws.Cells(1, 1), Ws.Cells(Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row, 1))
For Each Cell In Ws.Range(Ws.Cells(1, 2), Ws.Cells(Ws.Cells(Ws.Rows.Count, 2).End(xlUp).Row, 2))
Option Explicit Sub CollectMainItems() Dim Ws As Worksheet, OutputWs As Worksheet Dim Cell As Range Dim TempArray As Variant Dim Prefix As String Dim NextRow As Long Dim Result(1 To 4) As Variant With ThisWorkbook.Worksheets Set OutputWs = .Add(, .Item(.Item(.Count).Name)) End With With OutputWs .Name = "Output" .Cells(1, 1) = "Sheet Name" .Cells(1, 2) = "Section Title" .Cells(1, 3) = "Item Code" .Cells(1, 4) = "Quantity" End With For Each Ws In ThisWorkbook.Worksheets Result(1) = Ws.Name For Each Cell In Ws.Range(Ws.Cells(1, 2), Ws.Cells(Ws.Cells(Ws.Rows.Count, 2).End(xlUp).Row, 2)) If Trim(Cell) = "Materials" Then Result(2) = Cell.Offset(-1, 0) End If If Not Cell = Empty Then TempArray = Split(Trim(Cell), "-") Prefix = TempArray(0) If Prefix = "EDU" Or Prefix = "WS" Then Result(3) = Trim(Cell) Result(4) = Cell.Offset(0, 5) NextRow = OutputWs.Cells(OutputWs.Rows.Count, 1).End(xlUp).Row + 1 OutputWs.Range(OutputWs.Cells(NextRow, 1), OutputWs.Cells(NextRow, 4)) = Result End If End If Next Cell Next Ws End Sub