Call a macro within another one

[Domroy]

Hi,

I have this macro:

Sub FilterIt()
  Dim Ws As Worksheet
  Dim Source As Range, Dest As Range
  
  'Refer to the data source sheet
  With Sheets("Active Listings")
    'Prepare to be sure
    If Not .AutoFilterMode Then
      MsgBox "Create an Autofilter and try again"
      Exit Sub
    End If
    'Clear all filters
    .AutoFilter.ShowAllData
    
    'Screen off, runs faster
    Application.ScreenUpdating = True
    
    'Check all worksheets
    For Each Ws In Worksheets
      'Skip our data sheet
      If Ws.Name = .Name Then GoTo Skip
      'Filter the data
      .AutoFilter.Range.AutoFilter 4, Ws.Name & "*"
      'Get the data
      Set Source = GetAutoFilterRange(.Range("A1").Parent, False)
      'Got any?
      If Source Is Nothing Then GoTo Skip
      'Skip if we only have a header
      If Source.Areas.Count = 1 And Source.Rows.Count = 1 Then GoTo Skip
      'Refer to the destination cell
      Set Dest = Ws.Range("A3")
      'Clear previous results
      Dest.CurrentRegion.ClearContents
      'Copy the new data
      Source.Copy
      Dest.PasteSpecial xlPasteValues
      'Copy off
      Application.CutCopyMode = False
    
Skip:
    Next
    
    'Clear all filters
    .AutoFilter.ShowAllData
  End With
End Sub

Private Function GetAutoFilterRange(Optional ByVal Parent As Object, _
    Optional WithoutHeader As Boolean = True) As Range
  'Returns the visible range of an Autofilter, Excel 2010 and above
  Dim R As Range

  If Parent Is Nothing Then
    Set Parent = ActiveSheet
    If Parent Is Nothing Then Exit Function
  End If

  'No filter, return nothing
  If TypeOf Parent Is Worksheet Then
    If Not Parent.AutoFilterMode Then Exit Function
  ElseIf TypeOf Parent Is ListObject Then
    If Parent.AutoFilter Is Nothing Then Exit Function
  Else
    Err.Raise 438, "GetAutoFilterRange", "Object " & TypeName(Parent) & " not supported"
  End If
  
  With Parent.AutoFilter
    'Get the whole range
    Set R = .Range
    'Remove headings?
    If WithoutHeader Then
      If R.Rows.Count = 1 Then Exit Function
      Set R = R.Resize(R.Rows.Count - 1).Offset(1)
    End If
    'Filter active?
    If .FilterMode Then
      'Error's off, we get an error if no cells are visible
      On Error GoTo ExitPoint
      Set R = R.SpecialCells(xlCellTypeVisible)
    End If
  End With
  'Return the result
  Set GetAutoFilterRange = R
ExitPoint:
End Function

I'd like to call the macro called "FormatDataTable" into this so that each time this macro pastes my data into the next sheet, it runs that macro on it before doing the next action. OR I'd like it to run on all the (non-hidden) sheets in the workbook, except the "Active Listings" sheet. I know there's a simple way. And I tried to "Call .FormatDataSheet" but it bugged out. I'm sure I just put it in the wrong place. Help! Please and thank you!

Judi

 

[hateme28]

post your complete code, what the error is that you're getting, and what line you're getting the error.

 

[Domroy]

OK - I figured out one of my errors - I had named the macro wrong. BUT, then when I tried to call the macro, it tried to run it on all tabs. I want it to run on all tabs EXCEPT "Active Listings".

Code:

Option Explicit

Sub FilterIt()
  Dim Ws As Worksheet
  Dim Source As Range, Dest As Range
  
  'Refer to the data source sheet
  With Sheets("Active Listings")
    'Prepare to be sure
    If Not .AutoFilterMode Then
      MsgBox "Create an Autofilter and try again"
      Exit Sub
    End If
    'Clear all filters
    .AutoFilter.ShowAllData
    
    'Screen off, runs faster
    Application.ScreenUpdating = False
    
    'Check all worksheets
    For Each Ws In Worksheets
      'Skip our data sheet
      If Ws.Name = .Name Then GoTo Skip
      'Filter the data
      .AutoFilter.Range.AutoFilter 4, Ws.Name & "*"
      'Get the data
      Set Source = GetAutoFilterRange(.Range("A1").Parent, False)
      'Got any?
      If Source Is Nothing Then GoTo Skip
      'Skip if we only have a header
      If Source.Areas.Count = 1 And Source.Rows.Count = 1 Then GoTo Skip
      'Refer to the destination cell
      Set Dest = Ws.Range("A3")
      'Clear previous results
      Dest.CurrentRegion.ClearContents
      'Copy the new data
      Source.Copy
      Dest.PasteSpecial xlPasteValues
      'Copy off
      Application.CutCopyMode = False
Skip:
    Next
    
    'Clear all filters
    .AutoFilter.ShowAllData
  End With
End Sub

Private Function GetAutoFilterRange(Optional ByVal Parent As Object, _
    Optional WithoutHeader As Boolean = True) As Range
  'Returns the visible range of an Autofilter, Excel 2010 and above
  Dim R As Range

  If Parent Is Nothing Then
    Set Parent = ActiveSheet
    If Parent Is Nothing Then Exit Function
  End If

  'No filter, return nothing
  If TypeOf Parent Is Worksheet Then
    If Not Parent.AutoFilterMode Then Exit Function
  ElseIf TypeOf Parent Is ListObject Then
    If Parent.AutoFilter Is Nothing Then Exit Function
  Else
    Err.Raise 438, "GetAutoFilterRange", "Object " & TypeName(Parent) & " not supported"
  End If
  
  With Parent.AutoFilter
    'Get the whole range
    Set R = .Range
    'Remove headings?
    If WithoutHeader Then
      If R.Rows.Count = 1 Then Exit Function
      Set R = R.Resize(R.Rows.Count - 1).Offset(1)
    End If
    'Filter active?
    If .FilterMode Then
      'Error's off, we get an error if no cells are visible
      On Error GoTo ExitPoint
      Set R = R.SpecialCells(xlCellTypeVisible)
    End If
  End With
  'Return the result
  Set GetAutoFilterRange = R
ExitPoint:
End Function

Someone wrote it for me (hence the comments). Can we remove the error to turn on autofilter, and just have the macro make sure it's on, while we're fixing it? The macro I'm trying to call is "DataTable" - and I'd like it to run on everything BUT the Active Listings tab.

Thank you for your help!