Getting the Current Table Name

[Jeffrey Mahoney]

I'm starting to see the value of using Excel Tables rather than dynamic named ranges. I know I can get ranges and headers from the table by direct reference. How do I get the name of the current Table?

I have 3 sheets, each with an Excel Table laid out with the exact same headers. Open Items, Approved Items, Completed Items. Lots of Sheet triggers are built in and I need to know what table name is from the target cell.

So I can use it like this:

TblName = CurrentTable                                       <<<< Unknown
Set R = Range(TblName & "[[Quote]:[Doc]]"

Sub Macro9()
  Dim R As Range

  Range("ApprovedItems_tbl[[Quote]:[Doc]]").Select

  Set R = Range("ApprovedItems_tbl[[Quote]:[Doc]]")

End Sub

[/Quote]

 

[Fluff]

How about

  Dim Tbl As ListObject
  Set Tbl = ActiveCell.ListObject
  Debug.Print Tbl.Name

 

[Jeffrey Mahoney]

Cool! Thank you

Is there a way to use Tbl as an object to get the columns without using the Tbl.name? Like Tbl.range("[Site Name"]") OR Tbl.range("Site Name")? What is Tbl.ListColumns() do?

 

[Fluff]

How about

  Tbl.ListColumns("site Name").DataBodyRange.Select

 

[Jeffrey Mahoney]

Ah, there it is. Much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Jeffrey Mahoney]

I am trying to search the MS reference before asking. I need some more input. I'm trying to get a range between to columns in the table. I know I can get that using the first method I posted, but how do I do it using the table as a listobject? I can get a single column.

Sub Test()
  Dim Tbl As ListObject
  Dim LC As ListColumns
  
  Set Tbl = ActiveCell.ListObject
  Set LC = Tbl.ListColumns
  
  LC("Site Name").DataBodyRange.Select
  Range(LC("Site Name").DataBodyRange, LC("Vendor").DataBodyRange).Select     '<<<<<<< Error
  Debug.Print LC("Site Name").Index
  
End Sub

 

[YasserKhalil]

@jeffrey I tried your code and I didn't get an error as you referred to. Can you give us a snapshot of the table structure?

 

[Fluff]

Check that the Vendor column is the correct name.
What error do you get?

 

[Jeffrey Mahoney]

MWR Project Tracker5.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2	State	Site Name	Discipline	Description	Vendor	Owner	Status	Orig WO ID	CM WO ID	Quote	Doc	Quoted Price	Inception Date	Request Approval Date	Approval Date	Scheduled Date	Completed Date	Cancelled Date	Last Status Change	Inception To Request Approval	Approval Period	Approval to Completion	Total Days
3	UT	Wilmington	HVAC	Four Compressors for RTU#4 are down Replace only units #1 and #3. #5 is under warranty. Decision to replace units around August - September From CS: September 2019 we replaced compressors #2 & #7 Now we are reporting that compressors #1, #3, #4 need to be replaced, and #5 is noisy * Working with Comfort Sytems to determine which units are under warranty * Compressor #5 should be under Warranty * 2019 Compressors #2 and #7 were replaced in September * 7 Compressors on unit #3 and #4 need to be replaced. #5 is noisy * Need CS to audit all the Compressors and update the details before accepting quote *Quote submitted to Liz 6/8 *Investigating compressor sub-assets	Comfort Systems USA Intermountain	Tom	Pending Customer Approval			Quote 060		$ 26,733	6/8/2020						6/30/2020	23			23
Open Items