CODEWITHSUNDEEP
excelexcel-2007vba
Brian McDougall
Brian McDougall

Reputation: 71

Referencing Active Table in VBA

I'm creating a macro to manipulate a table in excel. I've been able to create code to do what I need, the problem is, my table names might not always be the same.

As an example:

Range("DATA_INPUT4[MFG Catalog]").Copy

This code copies the MFG Catalog column in my table just fine, but the table isn't always going to be named "DATA_INPUT4".

I've played around with a few things to try to get it to work with the active table and I feel like I'm close, but I can't get any progress. I've tryed:

Sub Copy_Active_Table()

Dim activeTable As String

activeTable = ActiveSheet.ListObjects(1).Name

MsgBox activeTable 'To make sure it's pulling the correct table name

Range("activeTable[MFG Catalog]").Copy

End Sub

This is probably completely wrong, but you can see where I'm going with it.

I have a feeling that I have to Dim activeTable As ListObject but I haven't been able to figure that out either.

This seemingly simple problem is driving me nuts, any help would be awesome.

Thanks,

Brian

Upvotes: 2

Views: 16187

Answers (3)

HHFox
HHFox

Reputation: 329

Just had a similar problem and ended up here. Along the lines of @choukkos's answer, you can also use the ListObject's properties (which is even a bit faster as it doesn't need to do the name resolution twice)

ActiveCell.ListObject.ListColumns("MFG Catalog").DataBodyRange.Copy

in the same manner other table data can also be extracted...

Set t = ActiveCell.ListObject
'copy line 3 of the table
t.ListRows(3).Range.Copy
'select current line
t.ListRows(Selection.Row - t.Range.Row).Range.Select

Upvotes: 0

chukko
chukko

Reputation: 460

For those who look for a solution to reference active table, the solution is:

ActiveCell.ListObject.Name

Other solutions fix problem in the question (using variable to refer to the table) - but the script in the question does NOT refer to the active table - but rather the first table in the sheet (which might be the same as active table).

Upvotes: 3

Brian McDougall
Brian McDougall

Reputation: 71

Answered in the comments,

Yes indeed, you are very close. Try this: Range(activeTable & "[MFG Catalog]").Copy

Ralph

Thanks, Ralph.

Upvotes: 1

Related Questions