Forum Discussion
Solved
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....
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)
I don't understand
ActiveCell.Offset(0, -1).Range("Master_Template[[#Headers],[TYPE]:[LICENSE FLAG]]").Select
What does that do?
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.
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.
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)Worked as needed, thank you very much once again!