Reputation: 31
I have a workbook which contains approximately 40 tables. The tables are very disorganised in every file, so you never know where the table might be located in the worksheet. In some worksheets it might be that you have even 2 or 3 tables. Furthermore, the tables are very unstructured, so that you are not capable to know where will always be the rows index, or column index. I do Need to Loop trough all Sheets and extract first the data, and then to compute for every cell the cellcode which is a combination of row index, col index and the title of the table.
The desired Output shall look something like this: which means, in table 04.03.1, row005, column010 I have data: 132151300
{EF04.03.1,r005,c010} 132151300
I did that using VBA and named ranges but the problem is that every time I receive a new file, I have to define the named ranges again for all 40 tables. The new file might not have the same structure as the one I already defined so I am not able to simply transfer the ranges from one workbook to another.
Any Suggestion about how can I identify automatically the Content and then col index/row index? Or any other workaround?
Upvotes: 3
Views: 4594
Reputation: 2015
Here is some code to iterate through each sheet in a workbook and then go through each cell in each table. The messagebox give the sheet name, table range, table name, value of the cell, row and column . You didn't state where you wanted the information to go, but this will get you the information, you just have to decide where it's going and format it to your requirement.
Sub FindAllTablesinWB()
Dim oSh As Worksheet
Dim oLo As ListObject
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each oSh In wb.Worksheets
For Each oLo In oSh.ListObjects
For col = 1 To oLo.ListColumns.Count
For rw = 2 To oLo.ListRows.Count
MsgBox "Table: " & oSh.Name & ", " & oLo.Range.address & ", " & oLo.Name & ", " & oLo.Range.Cells(rw, col).Value & ", " & "Row " & rw & " Column " & col
Next rw
Next col
Next oLo
Next oSh
End Sub
Upvotes: 1