Combining two macros - why won't they work?

Hi all,

I'd appreciate you thought on the attached sheet. I have two macros. A worksheet macro that filters the table according to cell M1. And, in addition to that, I have a macro that sorts the table simply by click on the header cell. Both work separately, however, I need both to work in unison so that I have a filter and and sorting functionality.

Please let me know your thoughts, any help would be appreciated!

Thanks,

Mike

Hi mehmetcik,

Thanks for looking! Sorry, you're right, the other is a module, which obviously hasn't carried through with the worksheet. Please see the additonal modules I am using below.

Sub SetupOneTime()

'adds rectangle at top of each column
'code written by Dave Peterson 2005-10-22
  Dim myRng As Range
  Dim myCell As Range
  Dim curWks As Worksheet
  Dim myRect As Shape
  Dim iCol As Integer
  Dim iFilter As Integer
  iCol = 12  'number of columns
' 2010-Oct-31 added space for autofilter dropdowns
' set iFilter to 0 if not using autofilter
  iFilter = 12 'width of drop down arrow
  
  Set curWks = ActiveSheet

  With curWks
      
    Set myRng = .Range("a1").Resize(1, iCol)
    For Each myCell In myRng.Cells
        With myCell
          Set myRect = .Parent.Shapes.AddShape _
              (Type:=msoShapeRectangle, _
              Top:=.Top, Height:=.Height, _
              Width:=.Width - iFilter, Left:=.Left)
        End With
        With myRect
          .OnAction = ThisWorkbook.Name & "!SortTable"
''        2010-Oct-31 revised to fill shapes in Excel 2007
''          .Fill.Visible = False
          .Fill.Solid
          .Fill.Transparency = 1#
          .Line.Visible = False
        End With
    Next myCell
  End With
End Sub

And the second part (a separate module), I have highlighted in red the line of code that VBA editor flags as problematic. As said, these two work fine on their own, however this issue occurs when trying to run them in the attached sheet (which has an additional worksheet macro):

Sub SortTable()
  'code written by Dave Peterson 2005-10-22
  '2006-08-06 updated to accommodate hidden rows
  Dim myTable As Range
  Dim myColToSort As Long
  Dim curWks As Worksheet
  Dim mySortOrder As Long
  Dim FirstRow As Long
  Dim TopRow As Long
  Dim LastRow As Long
  Dim iCol As Integer
  Dim strCol As String
  Dim rng As Range
  Dim rngF As Range

  TopRow = 1
  iCol = 7  'number of columns in the table
  strCol = "A"  ' column to check for last row

  Set curWks = ActiveSheet

  With curWks
    LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
    If Not .AutoFilterMode Then
        Set rng = .Range(.Cells(TopRow, strCol), _
                .Cells(LastRow, strCol))
    Else
        Set rng = .AutoFilter.Range
    End If
    
    Set rngF = Nothing
    On Error Resume Next
    With rng
       'visible cells in first column of range
       Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
           .SpecialCells(xlCellTypeVisible)
    End With
    On Error GoTo 0
    
    If rngF Is Nothing Then
         MsgBox "No visible rows. Please try again."
         Exit Sub
    Else
         FirstRow = rngF(1).Row
    End If
        
    myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
    
    Set myTable = .Range(strCol & TopRow & ":" _
        & strCol & LastRow).Resize(, iCol)
    If .Cells(FirstRow, myColToSort).Value _
      < .Cells(LastRow, myColToSort).Value Then
        mySortOrder = xlDescending
    Else
        mySortOrder = xlAscending
    End If
    
    myTable.Sort key1:=.Cells(FirstRow, myColToSort), _
              order1:=mySortOrder, _
              header:=xlYes
  End With

End Sub

Any advice would be greatly appreciated!

Thanks,

Mike