Referencing Active Table in VBA

Question

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

Solution 1

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).

Solution 2

Answered in the comments,

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

Ralph

Thanks, Ralph.