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

 

[bmacias]

Please excuse my naiveness but should you call it with Call FormatDataTable instead of Call .FormatDataSheet?

 

[Domroy]

Typo on my part. I am just not sure where to put it...but yes, I'd type it correctly! LOL

 

[rlv01]

I'm confused. I don't see "FormatDataTable" or "FormatDataSheet" in the posted code.

 

[Domroy]

I haven’t put it in there yet. Because I don’t know where the line goes. I tried putting it after the paste function, but it bugged out. So I deleted it when I posted the code here.

 

[hateme28]

I think you want something like this. After your code pastes to dest. it will call the sub or function format data table. Be sure to create that sub before running the code or it will error out. I’d also suggest rewriting your if statements. You can reword them in order to exclude goto skip.
Ex. If not ws.name =.name then
code
End if

   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
      Call FormatDataTable()
Skip:
    Next

 

[Domroy]

I'm sure it's user error...but this didn't work.

 

[Domroy]

And by "user" - I mean me. But I'm not good enough yet to make this work right.

 

[hateme28]

If you're going to do a second macro. you should record/write that macro first. make sure that macro will do what you expect it to do. then make the two work together. The first macro runs a Subroutine or Sub called FilterIt(). within this Sub, you need to make a call to the Sub of the other macro, which you have said is FormatDataSheet. In order to do make the call you would use the syntax Call <sub name>(). so in this case it would be Call FormatDataSheet(). but if this Sub doesn't exist, it will cause an error.

 

[Domroy]

It does exist. But I must be doing it wrong.

What if we just created a macro to run the “FormatDataTable” macro on all the tabs except the Acive Listings Tab. Can we do that?