MeisterConrad
New Member
- Joined
- Jan 17, 2017
- Messages
- 42
- Office Version
- 2007
I hope you can help me; I just can't see my way through it.
I have a series of 20 identical tables (Table1, Table2, etc.), arrange from left to right on one worksheet.
Each of these tables has a merged cell containing concatenated data that spans across the top of the table. This merged cell serves as a sort of pre-header/nameplate for the table.
I'm trying to make code on the worksheet that recognizes when this nameplate cell is clicked, then the associated table gets data-sorted. But the code is to recognize when any of the nameplate cells are selected.
I recorded a macro for the data-sort operation:
The code I came up with to identify the table based on the nameplate cell being selected is:
For one problem, I need the code to recognize if the cell contains the text "Table" and the number assigned to i, forgetting about the rest of the text in the cell.
But I also need to join these two bits of code into one fell swoop. And something doesn't feel quite right.
Am I not referencing the tables properly?
I'm really trying to clean up a lot of redundant code from a previous version of this workbook, so I kinda wanted to make this a sub-routine that can also be triggered in other operations. Thanks in advance for your help.
I have a series of 20 identical tables (Table1, Table2, etc.), arrange from left to right on one worksheet.
Each of these tables has a merged cell containing concatenated data that spans across the top of the table. This merged cell serves as a sort of pre-header/nameplate for the table.
I'm trying to make code on the worksheet that recognizes when this nameplate cell is clicked, then the associated table gets data-sorted. But the code is to recognize when any of the nameplate cells are selected.
I recorded a macro for the data-sort operation:
VBA Code:
Sub Macro2()
'
' Macro2 Macro
' Select a Table from the RANGE ID box, then data-sort the Table.
'
'
Application.Goto Reference:="Table2"
ActiveWorkbook.Worksheets("LedgerTablesSheet").ListObjects("Table2").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("LedgerTablesSheet").ListObjects("Table2").Sort. _
SortFields.Add Key:=Range("Table2[Date Cleared]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("LedgerTablesSheet").ListObjects("Table2").Sort. _
SortFields.Add Key:=Range("Table2[Trans-action Date]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("LedgerTablesSheet").ListObjects("Table2").Sort. _
SortFields.Add Key:=Range("Table2[Budget Category]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("LedgerTablesSheet").ListObjects("Table2").Sort. _
SortFields.Add Key:=Range("Table2[SubCategory]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("LedgerTablesSheet").ListObjects("Table2").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
The code I came up with to identify the table based on the nameplate cell being selected is:
VBA Code:
Dim TableToSort As ListObject
Dim i As Integer
For i = 1 to 20, step 1
Set TableToSort = ActiveSheet.ListObjects("Table" & i)
If ActiveCell.Text = "Table" & i Then
TableToSort = ActiveSheet.ListObjects("Table" & i)
Exit For
End If
Next i
For one problem, I need the code to recognize if the cell contains the text "Table" and the number assigned to i, forgetting about the rest of the text in the cell.
But I also need to join these two bits of code into one fell swoop. And something doesn't feel quite right.
Am I not referencing the tables properly?
I'm really trying to clean up a lot of redundant code from a previous version of this workbook, so I kinda wanted to make this a sub-routine that can also be triggered in other operations. Thanks in advance for your help.
Last edited by a moderator: