PDA

View Full Version : Macro to select unknown, named table



ethicalmortg
06-04-2011, 11:14 AM
:banghead:
Every week, I get 5 different Excel workbooks that each contain 1 table only. The table is always named, but I can't be positive that it's the same name constantly. The table always starts in cell A1. One of the workbooks I receive contains 3 columns of data, and I delete Column B because the data is irrelevant for my purposes. The other 4 workbooks only contain 2 columns of data, Column A data is always eight numbers, and Column B is always a date dd/mm/yy.

I need to convert each table to a data range, select the data range and rename that "Table1". The reason why is because when I get the 5 different Excel workbooks, the data range may be longer than the table, and I want to capture all available data into a new table (I don't know how to extend an existing table to the last filled row).

For instance, the table may be Row 1 through 10, but the data may be from Row 1 through 15.

Once I am sure all the data is captured into a table, I add a few rows of text. Then, I cut and paste the values into a new, larger Excel workbook that aggregates all the data and sorts it.

:dunno

Aussiebear
06-04-2011, 03:09 PM
This may get you started on the right track
Sub SelectRename()
Worksheets("Sheet1").UsedRange.Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$20"), , _
xlNo).Name = "Table1"
Columns("B:B").Delete Shift:=xlToLeft
End Sub

The used range in this case was A1:F20.