VBA MsgBox to Locate Tables by Name, Copy Paste to Compile All into a Single Table

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
The code I'm using is within Excel -- it extracts tables out of multiple Word docs within a folder at once.
It's awesome and even inserts a color bar (colorized/filled row to separate the long batch of tables within 1 sheet).

The results are all stacked within a single sheet of the workbook starting with Column A on Row 4 (Row 3 is the first gray color bar indicating the top of the first table).
*There's a colorized/filled row that separates each table.*
Need the ability to use a MSGBOX to call out a specific table name and
have every occurrence of that table name existing -- extracted and pasted into the next available empty sheet.
(it's taking a bunch of separated tables, finding all that are equal to X and copying them all to another sheet to create one big table)

Example:
6 Word docs, let's say each file was named a diff car:
.......MercedesGLE350SUV.docx, MercedesCL.docx, MercedesS.docx, etc.

Each docx holds a variety of tables such as below:
The same table names are used throughout each docx file in many/most but not all cases (but that's ok).
....WHEEL parts table
....ENGINE parts table
....WIPER BLADE parts table
....EXHAUST system parts table
....CONVERTABLE parts table
....FLUIDS system parts table
All of these tables get pasted consecutively into the Excel sheet that I'm needing the MsgBox to search through --

The analyst needs the ability to plug into the MsgBox the name of a table: "ENGINE"
(and the code run through column A of the active spreadsheet to locate that name)
Once located, it needs to extract all the content that's sitting between 2 colorized rows that are above and below the table... paste it into the next avail spreadsheet... then go back to the orig listing and keep looking for another 'hit' that is equal to that same table name "ENGINE" --
-- if found, copy THAT table (content residing between the bars) and go paste it into that other sheet to continue building one large ENGINE table full of all the ENGINE parts from all the tables named "ENGINE".

Hope that makes sense!?
I'm working with code tables not car parts - but figured the concept of using cars would make it easiest to grasp the need.

Here's a clip of the code that's putting the color bars in place so you can see exactly what color - assuming the code with the MsgBox has to be told to only take what's between those 2 colorized rows until it finds another occurrence and record that chunk as well.
Code:
    For tableStart = 1 To tableTot
      With .Tables(tableStart)
        'copy cell contents from Word table cells to Excel cells
        For iRow = 1 To .Rows.Count
          For iCol = 1 To .Columns.Count
            wkSht.Cells(resultRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
          Next iCol
          resultRow = resultRow + 1
        Next iRow
      End With
      resultRow = resultRow + 1
      With wkSht
        .Range(.Cells(resultRow, 1), .Cells(resultRow, iCol)).Interior.ColorIndex = 15
      End With
      resultRow = resultRow + 1
    Next tableStart
End With

End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

portews

Active Member
Joined
Sep 4, 2009
Messages
303
The easiest way to do (what I think) you want, is to Autofilter the column and copy what still shows. The best way to get the criteria is to use a combobox populated with the unique values from the column.
So... You'll need to create a Userform with a combobox and two buttons (Submit and Cancel) in it and get the value from the combobox to feed the filter and the new sheet names.

Read the comments, you may have to change a few ranges and sheet names where indicated.

Try this:

Put this in the Userform code page.

Code:
Private Sub CommandButton1_Click()
[COLOR=#00ff00]'Submit button[/COLOR]
[COLOR=#00ff00]    'get the combo box value[/COLOR]
    filtervalue = UserForm1.ComboBox1.Value
    Unload UserForm1
End Sub


Private Sub CommandButton2_Click()
[COLOR=#00ff00]'Cancel button[/COLOR]
    End
End Sub

Put this in a main module:

Code:
Global filtervalue As String

Sub CopyFilter()
Dim c As Range
Dim rng As Range
Dim LR As Long
Dim LR2 As Long
Dim MasterSht As Worksheet
Dim i
Dim cl As Range, Dic As Object
Dim wsTest As Worksheet


[COLOR=#00ff00]'set sheet name as needed[/COLOR]
Set MasterSht = Sheets("Sheet2")


Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=#00ff00]'stop showing screen changes[/COLOR]
Application.ScreenUpdating = False
MasterSht.Activate
Range("A1").Select
[COLOR=#00ff00]'Turn the filter off, if it's on[/COLOR]
If MasterSht.FilterMode Then
    MasterSht.AutoFilterMode = "False"
End If
[COLOR=#00ff00]
[/COLOR]
[COLOR=#00ff00]'for each row in the the date column[/COLOR]
For Each cl In MasterSht.Range("A4:A" & MasterSht.Cells(Rows.Count, "A").End(xlUp).Row)
[COLOR=#00ff00]    'if the date is not in the list[/COLOR]
    If cl > "" And Not Dic.Exists(cl.Value) Then
[COLOR=#00ff00]        'add the date and the price[/COLOR]
        Dic.Add cl.Value, 0
        i = i + 1
[COLOR=#00ff00]        'populate the combo box with unique values[/COLOR]
        UserForm1.ComboBox1.AddItem (cl)
    End If
Next cl
[COLOR=#00ff00]'show the userform to select the value[/COLOR]
UserForm1.Show


[COLOR=#00ff00]'check to see if the sheet exists[/COLOR]
On Error Resume Next
Set wsTest = ActiveWorkbook.Sheets(filtervalue)
On Error GoTo 0
[COLOR=#00ff00]'if the sheet doesn't exist, create it at the end.[/COLOR]
If wsTest Is Nothing Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = filtervalue
[COLOR=#00ff00]    'copy the header information (change as needed)[/COLOR]
    MasterSht.Range("A2:M2").Copy
    Sheets(filtervalue).Range("A1").PasteSpecial Paste:=xlPasteValues
End If
[COLOR=#00ff00]'find the last used row in A column of the named sheet[/COLOR]
LR2 = Sheets(filtervalue).Cells(Rows.Count, "A").End(xlUp).Row + 1


With MasterSht
    .Activate
[COLOR=#00ff00]    'find the last used row in A column in the master sheet[/COLOR]
    LR = .Cells(Rows.Count, "A").End(xlUp).Row
[COLOR=#00ff00]    'the top row MUST have a title in it.[/COLOR]
    Set rng = .Range("A2:M" & LR)
    
[COLOR=#00ff00]    'filter the rows & copy[/COLOR]
    rng.AutoFilter field:=1, Criteria1:=filtervalue
    .AutoFilter.Range.Select
    Selection.Offset(1).Resize(Selection.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
[COLOR=#00ff00]    'paste the values[/COLOR]
    Sheets(filtervalue).Range("A" & LR2).PasteSpecial Paste:=xlPasteValues
    
[COLOR=#00ff00]    'Turn the filter off, if it's on[/COLOR]
    If .FilterMode Then
      .AutoFilterMode = "False"
    End If
[COLOR=#00ff00]    'show screen changes[/COLOR]
    Application.ScreenUpdating = True
    .Activate
    .Range("A1").Select
End With
End Sub

We can also automatically create the sheets needed, instead of selecting them from the combo box, but you said you wanted the messagebox, so that's the way I went.
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
Thanks greatly Bill -- funny you mentioned this -- It came to mind yesterday - I had gone and created a UserForm with the Submit & Cancel buttons but was not using a ComboBox - I was trying to use a basic input box for a search word/string to be entered - messed w/ it about 5 hours then gave up, deleted it and began search posts w/ frustration again =-(

This morning, I've started fresh and created a new test file with ComboBox as you directed.
I'm excited that it seems this could be the solution - once the following is resolved (crossing fingers)!

=I made sure a header was within A1 and proceeded as follows:
=The one chunk of code is pasted within a 'Module1' of it's own --
=The other 2 button chunks - I double clicked on each button and put each chunk in accordingly -- (haven't messed w/ UserForms in like 10 yrs so I'm hoping I did this properly) -- and THINK I DID -- because when I hit "RUN" the Form comes up, I'm able to see data within the drop down -- and the CANCEL button works -- however, --
When select "TABLE 2" and click "SUBMIT" - I get an error and it throws me out..

Here's what I see -- hopefully, this detail can help you see what's happening --
Run-time error '1004': Application-defined or object-defined error

This is the line that's highlighted in yellow when I click: Debug
Sheets.Add(After:=Sheets(Sheets.Count)).Name = filtervalue

This is the section that this line resides within:
Code:
'check to see if the sheet exists
On Error Resume Next
Set wsTest = ActiveWorkbook.Sheets(filtervalue)
On Error GoTo 0
'if the sheet doesn't exist, create it at the end.
If wsTest Is Nothing Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = filtervalue
    'copy the header information (change as needed)
    MasterSht.Range("A2:M2").Copy
    Sheets(filtervalue).Range("A1").PasteSpecial Paste:=xlPasteValues
End If
I'm seeing it's successfully adding a new sheet each time I run it - but nothing is on it. (just FYI)
Let me know if you see what I might be doing wrong - and I'll get it updated appropriately...?
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
Here's a TABLEIZER view of what the sheet2 looks like - hoping a visual might help everyone as well...?
No idea why this post is showing so much white space at top - because the tableizer content is immediately below this sentence -- but anyway - here's an idea of how content is laid out...

(this is how it feeds in when I perform an import - sucking in tons of tables from coders who've created tons of word docs - holding their code tables. (has nothing to do with high-end cars - but I used that analogy as a more easily understandable example to see the need for grabbing all the tables that met certain criteria..

(see under Table 2) - I want to be able to key in 'DOC' (or choose it from a ComboBox) and have the code grab that 'DOC' table (which consists of 6-7rows) as well as grab the next 'DOC' table's content (found under Table 6) and the next 'DOC' table's content found under (Table 8)... (to achieve creating a single-all-inclusive "DOC" table full of all these excerpts taking from multiple Word docs that have been laid into this Col A of a spreadsheet)

When I tested the code earlier I just chose a Table name to see if it would extract that one table -- but now that I'm looking at it --- I'm not sure if the ComboBox/Filtering will do the trick of grabbing out all the chunks equal to "DOC"... Maybe I'm just not seeing the vision yet ---
Look forward to hearing thoughts...
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>TABLES</th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th></tr></thead><tbody>
<tr><td>Tables from Word</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 1 - XXX</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOCIDEXPMAC-ELEMENT-TABLE</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td>Document</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>Document</td><td>Identifier</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>Identifier</td><td>Suffix</td><td>Explanation</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>D12345H</td><td>D</td><td>Non-Stock</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>D12345H</td><td>R</td><td>Inventory On Hold</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>D12345H</td><td>T</td><td>Inventory In Repair</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 2 - SSS</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC</td><td> </td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td> </td><td> </td></tr>
<tr><td>ID</td><td>SUF</td><td> TIV-11</td><td> TIV-12</td><td> TIV-13</td><td> TIV-14</td><td> TIV-15</td><td> TIV-16</td><td> TIV-17</td><td> TIV-18</td><td> TIV-19</td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>Z</td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>G</td><td> </td><td> </td><td> </td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>P</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>S</td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 3 - AAA</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>SCD-DISCNTL-REC</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC-ID</td><td>DT-CHG</td><td>DT-EFF</td><td>PROCESS-SEQ</td><td>TBL-CHG-IND</td><td>TRANS-IND-CODE</td><td>TRANS-LR-CODE</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>2003274</td><td>2003274</td><td>1</td><td> </td><td>1</td><td>5</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>2003274</td><td>2003274</td><td>1</td><td> </td><td>1</td><td>5</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 4 -JJJ</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>SCD-TIC-REC</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC-ID</td><td>DOC-ID-SUFFIX</td><td>DT-CHG</td><td>DT-EFF</td><td>PROCESS-SEQ</td><td>TBL-CHG-IND</td><td>TRANS-IND-CODE</td><td>TRANS-LR-CODE</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>Z</td><td>2003274</td><td>2003274</td><td>2</td><td> </td><td>2</td><td>1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>F</td><td>2003274</td><td>2003274</td><td>3</td><td> </td><td>AA</td><td>A1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>A</td><td>2003274</td><td>2003274</td><td>2</td><td> </td><td>2</td><td>1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>G</td><td>2003274</td><td>2003274</td><td>3</td><td> </td><td>AA</td><td>A1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 5 -CCC</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>AA Version 52</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td>Public</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td>Valid DoDAAC</td><td>Credit</td><td>Surplus</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ICC</td><td>ERRC</td><td>or Fund Code</td><td>Indicator</td><td>Code</td><td>TIV</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>8</td><td>N, P</td><td>NO</td><td> </td><td> </td><td>1</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>8</td><td>N, P</td><td> </td><td>A, Y</td><td> </td><td>2</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>8</td><td>N, P</td><td> </td><td>A, Y</td><td>Yes</td><td>12</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>8</td><td>N, P</td><td> </td><td> </td><td> </td><td>3</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>8</td><td>C, L, T</td><td>NO</td><td> </td><td> </td><td>4</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 6 -PPP</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC</td><td> </td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td></tr>
<tr><td>ID</td><td>SUF</td><td> TIV-11</td><td> TIV-12</td><td> TIV-13</td><td> TIV-14</td><td> TIV-15</td><td> TIV-16</td><td> TIV-17</td><td> TIV-18</td><td> TIV-19</td><td> TIV-20</td><td> TIV-21</td></tr>
<tr><td>DJN3336H</td><td>Z</td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>G</td><td> </td><td> </td><td> </td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>P</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>S</td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 7 -YYY</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>Document</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>Identifier/</td><td> </td><td>Condition</td><td>Debit</td><td> </td><td> </td><td>Credit</td><td> </td><td> </td><td> </td><td>Amount</td><td>Logic</td><td> </td></tr>
<tr><td>Suffix</td><td>TIV</td><td>Indicator</td><td>GLA</td><td>SA</td><td>AT</td><td>GLA</td><td>SA</td><td>AT</td><td>Amount</td><td>Rule</td><td>Rule</td><td>Version</td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>D21ZZR/J</td><td>1</td><td>A</td><td>1521</td><td>3P</td><td>1</td><td>7210</td><td>10</td><td>1</td><td>MAC</td><td>F</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td>F</td><td>1523</td><td>3P</td><td>3</td><td>7210</td><td>10</td><td>1</td><td>MAC</td><td>F</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td>2</td><td>A</td><td>1521</td><td>3P</td><td>1</td><td>6500</td><td>10</td><td>1</td><td>MAC</td><td>F</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td> </td><td>5100</td><td>9</td><td>11</td><td>1310</td><td>11</td><td>1</td><td>LAC</td><td>L</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td> </td><td>4132</td><td>20</td><td> </td><td>4251</td><td>21</td><td> </td><td>LAC</td><td>L</td><td>AA</td><td>52</td></tr>
<tr><td>TABLE 8 -WWW</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC</td><td> </td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td> </td><td> </td><td> </td></tr>
<tr><td>ID</td><td>SUF</td><td> TIV-11</td><td> TIV-12</td><td> TIV-13</td><td> TIV-14</td><td> TIV-15</td><td> TIV-16</td><td> TIV-20</td><td> TIV-25</td><td> </td><td> </td><td> </td></tr>
<tr><td>ZZZ-R5903336H</td><td>A</td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td>Yes</td><td>Yes</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ZZZ-R5903336H</td><td>B</td><td> </td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ZZZ-R5903336H</td><td>A</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>ZZZ-R5903336H</td><td>B</td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td>Yes</td><td>Yes</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>TABLE 9 -QQQ</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td>5</td><td>A</td><td>1521</td><td>3P</td><td>1</td><td>6500</td><td>10</td><td>1</td><td>MAC</td><td>F</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td> </td><td>5100</td><td>9</td><td>11</td><td>1310</td><td>11</td><td>1</td><td>CARCASS</td><td>C</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td> </td><td>4132</td><td>20</td><td> </td><td>4251</td><td>21</td><td> </td><td>CARCASS</td><td>C</td><td>AA</td><td>52</td></tr>
<tr><td> </td><td> </td><td> </td><td>3</td><td> </td><td> </td><td>6</td><td> </td><td> </td><td>CARCASS</td><td>C</td><td>AA</td><td>53</td></tr>
<tr><td> </td><td> </td><td>F</td><td>1523</td><td>3P</td><td>3</td><td>6500</td><td>10</td><td>1</td><td>MAC</td><td>F</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td> </td><td>5100</td><td>9</td><td>11</td><td>1310</td><td>11</td><td>1</td><td>CARCASS</td><td>C</td><td>AA</td><td>50</td></tr>
<tr><td> </td><td> </td><td> </td><td>4132</td><td>20</td><td> </td><td>4251</td><td>21</td><td> </td><td>CARCASS</td><td>C</td><td>AA</td><td>52</td></tr>
<tr><td> </td><td> </td><td> </td><td>3</td><td> </td><td> </td><td>6</td><td> </td><td> </td><td>CARCASS</td><td>C</td><td>AA</td><td>53</td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td></td></tr>
</tbody></table>
 

portews

Active Member
Joined
Sep 4, 2009
Messages
303

ADVERTISEMENT

OK. Yeah :eek: This is not at all what I had envisioned the page look like. I'm not surprised it doesn't work. I'll have to take another look at the problem.

Strictly parsing it out shouldn't be too bad, it just means looping thru the A cells and testing for instances of the word "TABLE" with a dash in a cell. If it finds one, it's a new sheet.

Question... what do you want the Tab name to say, what the cell says ("TABLE 1 - XXX") or just the name ("XXX").
Do you want to do all the tables automatically or just look for one at a time. All at once will be much easier.
 
Last edited:

portews

Active Member
Joined
Sep 4, 2009
Messages
303
Try this:

Code:
Sub parseWorksheet()
Dim MasterSht As Worksheet
Dim wsTest As Worksheet
Dim i, x, LR, LR2 As Integer
Dim TabName As String


[COLOR=#00FF00]'set sheet name as needed[/COLOR]
Set MasterSht = Sheets("Sheet2")
[COLOR=#00FF00]'find the last row[/COLOR]
For i = 1 To 13
    If MasterSht.Cells(MasterSht.Rows.Count, i).End(xlUp).Row > LR Then
        LR = MasterSht.Cells(MasterSht.Rows.Count, i).End(xlUp).Row
    End If
Next


For i = 3 To LR
[COLOR=#00FF00]    'if the A column starts with "TABLE"[/COLOR]
    If Left(MasterSht.Cells(i, 1), 5) = "TABLE" Then
[COLOR=#00FF00]        'find only the table name[/COLOR]
        TabName = Trim(Right(MasterSht.Cells(i, 1), Len(MasterSht.Cells(i, 1)) - InStrRev(MasterSht.Cells(i, 1), "-")))
[COLOR=#00FF00]        'check to see if the sheet exists[/COLOR]
        On Error Resume Next
        Set wsTest = ActiveWorkbook.Sheets(TabName)
        On Error GoTo 0
[COLOR=#00FF00]        'if the sheet doesn't exist, create it at the end.[/COLOR]
        If wsTest Is Nothing Then
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = TabName
        End If
    Else
[COLOR=#00FF00]        'copy the row from the master sheet[/COLOR]
        MasterSht.Range("A" & i & ":M" & i).Copy
[COLOR=#00FF00]        'find the last used row in A column of the named sheet[/COLOR]
        LR2 = Sheets(TabName).Cells(Rows.Count, "A").End(xlUp).Row + 1
[COLOR=#00FF00]        'put a number in the A column to easier find the last row[/COLOR]
        Sheets(TabName).Range("A" & LR2) = LR2
[COLOR=#00FF00]        'paste the row from the master sheet starting at the B column[/COLOR]
        Sheets(TabName).Range("B" & LR2).PasteSpecial Paste:=xlPasteValues
[COLOR=#00FF00]        'if row 1 is blank, delete it and set the counter to 1[/COLOR]
        If Application.CountA(Range("A1").EntireRow) = 0 Then Range("A1").EntireRow.Delete: Range("A1") = 1
    End If
Next


End Sub
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599

ADVERTISEMENT

Glad I was able to upload a sample - a picture is ALWAYS worth a thousand words -- wish the site allowed attachments (hoping they will again some day) - but anyway - the latest code is extremely useful -- parsing each of the tables out the way it has --- is something I definitely have to do quite often -- and love how it's naming the tabs as it goes!!

---but the goal with this task is to locate the "like" tables within that 1 tab and have them copied over to a single tab/sheet to build 1 table-- (all Engine parts found and copied over to one table)

-- whether it be defined in the code or via a msg box or combobox -- doesn't matter as long as it's achievable --
(my orig thought was it would be nice to not have to change the back end code every time a diff search/combine effort was needed -- but seriously -- I'm totally fine with that -- if that's the easiest way to achieve the goal ---

The problem is -- when I get these batches of tables all on one sheet, each table does not always have a table name ---
So I have to look for a unique identifier so the code can locate the desired tables and go COMBINE them....
See all the places where it says "Table 1 - XXX", I actually had to manually enter that to add separation for this example. (but wanted you to know in many cases, that added helpful info is not available in most cases - so here's what I do -- below to overcome that challenge)

So -- using my uploaded table as an example --
=The user could either enter the word "DOC" (or hard code it into the backend as the TARGET WORD(s) to FIND)
=The code would go locate the 1st "hit" in Table 2 and the 2nd "hit" in Table 6 --
=It would copy all the content between the words "TABLE" and go paste it onto a single sheet -- one right under the other -- to essentially build a single table that held "like" content.
All the DOC content should be in one table --

Using the color filled bar as the start/stop might be easier and better since Table names are not always there -- I could add them -- but using the COLOR bars at Start/Stops would be ideal if there was a way to tell the code to:
1-locate "DOC"
2-once found, copy all the content between the color bar above it and color bar below it -- and then paste it onto a blank sheet.
3-continue looking for another hit of "DOC" - once found, repeat step 2 (pasting it onto that same new sheet to essentially build 1 combined table of only "DOC" content...

(so if using the auto example, it would locate all instances of "ENGINE" and copy all engine parts to one sheet to build a sheet full of Engine parts)

Hope that helps!

If giving that new sheet a name is helpful -- that's fine - perhaps if the target word is "DOC" then when it finds a "hit" and goes to paste it onto a blank sheet - it could name it "DOC" so when the 2nd hit is found - it will know to keep pasting newly found content to the same "DOC" sheet... (it can find all at once or one by one -- doesn't matter as long as it all gets found and copied over to the one sheet.)

Same true if looking for ENGINE parts - it could name the tab ENGINE and paste all it's finds to that ENGINE tab.
 

portews

Active Member
Joined
Sep 4, 2009
Messages
303
OK. I have a cold and my mind is fuzzy from the medication. I went back and reread the entire thing and here's what I think you want. Enter in something that will find a whole cell match in the A column (it won't match DOC-ID when you put in DOC). When it finds that match, go back up till it hits a colored bar. Copy from that colored bar down to just before the next colored bar (that whole table) and paste it in another sheet with the name match. Go back to the main sheet and look for another match and copy that table as well. Correct?

If that's not correct, perhaps you can give me a sample table with the example of DJN3336H as the value you're looking for.
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
You are correct in every way.
DOC only (whole not partial match)
Only copy what's between the color bars
Copy to a single sheet
Find more matches, continue to copy to same single sheet
Sheet can be named whatever the search word(s) are if desired/if it helps to define paste to location or left unnamed -
But hey -- if you're sick-- go rest -- enjoy your chicken noodle soup --Lifetime channel or Basketball --lol-- and check back when you are feeling better! =-) Then again, I understand, if you're like me -- I'd rather be playing with stuff like this than glued to the tube - so whatever makes you happy.. Hope you're feeling better soon!
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
Using the large table above... Here's what the results should look like based on the requirements mentioned in the last post#9 (copying whatever is between the color bars in both finds....
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>SCD-TIC-REC</th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th><th> </th></tr></thead><tbody>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC-ID</td><td>DOC-ID-SUFFIX</td><td>DT-CHG</td><td>DT-EFF</td><td>PROCESS-SEQ</td><td>TBL-CHG-IND</td><td>TRANS-IND-CODE</td><td>TRANS-LR-CODE</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>Z</td><td>2003274</td><td>2003274</td><td>2</td><td> </td><td>2</td><td>1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>F</td><td>2003274</td><td>2003274</td><td>3</td><td> </td><td>AA</td><td>A1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>A</td><td>2003274</td><td>2003274</td><td>2</td><td> </td><td>2</td><td>1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DCX2900</td><td>G</td><td>2003274</td><td>2003274</td><td>3</td><td> </td><td>AA</td><td>A1</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DOC</td><td> </td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td><td>PUBLIC</td></tr>
<tr><td>ID</td><td>SUF</td><td>TIV-11</td><td>TIV-12</td><td>TIV-13</td><td>TIV-14</td><td>TIV-15</td><td>TIV-16</td><td>TIV-17</td><td>TIV-18</td><td>TIV-19</td><td>TIV-20</td><td>TIV-21</td></tr>
<tr><td>DJN3336H</td><td>Z</td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>G</td><td> </td><td> </td><td> </td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>P</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>DJN3336H</td><td>S</td><td> </td><td>Yes</td><td> </td><td> </td><td>Yes</td><td>Yes</td><td> </td><td>Yes</td><td> </td><td> </td><td> </td></tr>
<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td></td></tr>
</tbody></table>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top