Select active row of excel sheet using ListObject code

Hello,

Building off of a previous discussion.

I have an excel sheet that is used as a template to create additional sheets in an excel workbook.

This sheet has a table named Master_Template.

There is VBA code that references the header names of the table on the template excel sheet.

My initial problem was when this template sheet was copied, the table name changed, Master_Template1, Master_Template2, etc, and broke the VBA code.

A few lines of original code from this template sheet are:

Dim Module As Range
Dim MACHINE As Range

Set Module = Range("Master_Template[TYPE]")
Set MACHINE = Range("Master_Template[MACHINE]")

With suggestions, code changed to:

Dim Tbl As ListObject
    Dim Module As Range
    Dim MACHINE As Range

    Set Tbl = ActiveSheet.ListObjects(1)
    Set Module = Tbl.ListColumns("TYPE").DataBodyRange
    Set MACHINE = Tbl.ListColumns("MACHINE").DataBodyRange

and this corrected my initial problem.

I have another part of code:

Set rowselection = Selection
If Intersect(Module, rowselection) Is Nothing Then
MsgBox "Selected cell must be within the TYPE column."
ElseIf IsEmpty(ActiveCell.Value) Then
MsgBox "No data to clear in row " & ActiveCell.Row
Else
ActiveCell.Offset(0, -1).Range("Master_Template[[#Headers],[TYPE]:[LICENSE FLAG]]").Select
Selection.Copy Range("Master_Template[[#Headers],[TYPE]:[LICENSE FLAG]]").Offset(200, 0)

The bold part of the code no longer works due to the same table name change when sheet is copied, but works correctly on original template sheet.

I would like to still reference table column names because the number of columns could change in the table, but when this selection is made, it needs to be for the active row of the table only, not the entire table or the entire sheet row.

Thanks in advance.

Try this:

    Dim Hdr As Range
    Dim MyRow As Long
    Set Hdr = Range(Tbl.ListColumns("TYPE").Range(1), Tbl.ListColumns("LICENSE FLAG").Range(1))
    MyRow = ActiveCell.Row - Tbl.Range.Row
    Hdr.Offset(MyRow).Copy Destination:=Hdr.Offset(200)

4 Replies

HansVogelaar
MVP
Feb 26, 2023
chowell97

I don't understand

ActiveCell.Offset(0, -1).Range("Master_Template[[#Headers],[TYPE]:[LICENSE FLAG]]").Select

What does that do?
- chowell97
  Copper Contributor
  Feb 26, 2023
  It appears ActiveCell.Offset(0, -1) allows the entire active cell row to be selected.
  If removed, the entire table header row is selected instead of the active cell row.
  If changed to AcitiveCell.Offset(0, 0), the active cell row is selected, but the column selection is one off since the table starts in column B, not column A.
  
  Range("Master_Template[[#Headers],[TYPE]:[LICENSE FLAG]]").Select is selecting all of the active cell row.
  
  Selection.Copy Range("Master_Template[[#Headers],[TYPE]:[LICENSE FLAG]]").Offset(200, 0) is coping and pasting the selected table row 200 rows down.
  I am later deleting this copied row with additional code.
  
  I hope this helps answer your question.
  
  I do not know VBA and pieced together code found online and use snippets from macro recording until I get the outcome I am looking for.
  Was able to figure out everything I wanted to do for the template sheet, but now having problems with the way I wrote the code and the table name change with the creation of new sheets.
  With that said, if there is a better, cleaner way, please advise.
  
  Thanks in advance.
  - HansVogelaar
    MVP
    Feb 26, 2023
    chowell97
    
    Try this:
    
    Dim Hdr As Range Dim MyRow As Long Set Hdr = Range(Tbl.ListColumns("TYPE").Range(1), Tbl.ListColumns("LICENSE FLAG").Range(1)) MyRow = ActiveCell.Row - Tbl.Range.Row Hdr.Offset(MyRow).Copy Destination:=Hdr.Offset(200)

Forum Discussion

Select active row of excel sheet using ListObject code

4 Replies

Resources