Tweak Macro to Ignore or Remove Filtering

[welshraz]

Morning!

Hope someone can help me tweak this macro a little. It works for what I need, i.e. combining the data from 10 or so worksheets into a master tab, and ignoring a selection of others. What I need it to also do is ignore any filtering on the tabs it compiles, or remove the filtering before copying.

Sub combinedata()
Dim var As Integer
Dim Sh As Worksheet
var = 0
For Each Sh In Worksheets
If Sh.Name = "Master" Then
var = 1
Exit For
End If
Next Sh
For Each Sh In Worksheets
If Sh.Name <> ActiveSheet.Name And Sh.Name <> "Landing Page" And Sh.Name <> "Build Completes" Then
With Sh
.Range("A3:AC" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End With
End If
Next Sh
ActiveWindow.DisplayGridlines = False
Range("A3").CurrentRegion.Select
End Sub

Any help or guidance greatly received.

 

[earthworm]

Morning!

Hope someone can help me tweak this macro a little. It works for what I need, i.e. combining the data from 10 or so worksheets into a master tab, and ignoring a selection of others. What I need it to also do is ignore any filtering on the tabs it compiles, or remove the filtering before copying.

Sub combinedata()
Dim var As Integer
Dim Sh As Worksheet
var = 0
For Each Sh In Worksheets
If Sh.Name = "Master" Then
var = 1
Exit For
End If
Next Sh
For Each Sh In Worksheets
If Sh.Name <> ActiveSheet.Name And Sh.Name <> "Landing Page" And Sh.Name <> "Build Completes" Then
With Sh
.Range("A3:AC" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End With
End If
Next Sh
ActiveWindow.DisplayGridlines = False
Range("A3").CurrentRegion.Select
End Sub

Any help or guidance greatly received.

Try below

Sub combinedata()
Dim var As Integer
Dim Sh As Worksheet
var = 0
For Each Sh In Worksheets
If Sh.Name = "Master" Then
var = 1
Exit For
End If
Next Sh
For Each Sh In Worksheets
If Sh.Name <> ActiveSheet.Name And Sh.Name <> "Landing Page" And Sh.Name <> "Build Completes" Then
With Sh
Selection.AutoFilter
.Range("A3:AC" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End With
End If
Next Sh
Selection.AutoFilter
ActiveWindow.DisplayGridlines = False
Range("A3").CurrentRegion.Select
End Sub

 

[earthworm]

If the above does not work try to insert below line where you want the filter to remove.

Selection.AutoFilter

 

[welshraz]

T

Try below

Sub combinedata()
Dim var As Integer
Dim Sh As Worksheet
var = 0
For Each Sh In Worksheets
If Sh.Name = "Master" Then
var = 1
Exit For
End If
Next Sh
For Each Sh In Worksheets
If Sh.Name <> ActiveSheet.Name And Sh.Name <> "Landing Page" And Sh.Name <> "Build Completes" Then
With Sh
Selection.AutoFilter
.Range("A3:AC" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End With
End If
Next Sh
Selection.AutoFilter
ActiveWindow.DisplayGridlines = False
Range("A3").CurrentRegion.Select
End Sub

Thanks for this, but I have run it an it is still ignoring any of the filtered rows and not adding them to the master tab.

 

[earthworm]

ok got it

Insert below and tweak filter part as per your requirement . This macro will copy all visible filtered cells.

Dim Rng As Range
    Dim Rng_Del As Range

    'Apply autofilter to data range
    'Note: data must start in cell A1 for this macro to work
  
    Set Rng = Range("A1").CurrentRegion
    Rng.AutoFilter Field:=19, Criteria1:="FALSE", Operator:=xlOr, Criteria2:="=TRUE"
  
    'Copy visible rows assuming there's nothing else below the last row
  
    Rng.Offset(0, 0).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

 

[Fluff]

How about

Sub combinedata()
Dim var As Integer
Dim Sh As Worksheet
var = 0
For Each Sh In Worksheets
If Sh.Name = "Master" Then
var = 1
Exit For
End If
Next Sh
For Each Sh In Worksheets
If Sh.Name <> ActiveSheet.Name And Sh.Name <> "Landing Page" And Sh.Name <> "Build Completes" Then
With Sh
.AutoFilterMode = False
.Range("A3:AC" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End With
End If
Next Sh
ActiveWindow.DisplayGridlines = False
Range("A3").CurrentRegion.Select
End Sub