Hi Y'all,
I made a sub to copy and paste filtered excel data from one worksheet to another. It works for the most part, but on the function calls with an array the sub is skipping over the 4 starting values. Does this have to do with how I'm structuring my loop that runs over the array? Any help would be great, thanks!
Sub FilterAndCopy(sht As Worksheet, target As Worksheet, filterValue As String, filterRange As String)
Dim lastRow As Long
Dim selectFilterRange As range
Dim copyRange As range
Dim regEx As New RegExp
Dim arr() As String
'Filter and copy membership stats
sht.AutoFilterMode = False
lastRow = sht.range("A" & sht.Rows.Count).End(xlUp).Row
Set selectFilterRange = sht.range(filterRange & lastRow)
Set copyRange = sht.range("A2:K" & lastRow)
'Block below finds if multiple filter values are used
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "[,]"
End With
If regEx.TEST(filterValue) Then
arr() = Split(filterValue, ",")
Dim i As Integer
For i = LBound(arr) To UBound(arr)
selectFilterRange.AutoFilter Field:=11, Criteria1:=arr(i)
copyRange.SpecialCells(xlCellTypeVisible).Copy target.range("A2")
Next i
Else
selectFilterRange.AutoFilter Field:=11, Criteria1:=filterValue
copyRange.SpecialCells(xlCellTypeVisible).Copy target.range("A2")
End If
sht.ShowAllData
End Sub
'Call outside sub for each sheet in book
FilterAndCopy Sheets("Donations by Transaction"), Sheets("Memberships"), "TPWF Membership", "A1: K"
FilterAndCopy Sheets("Donations by Transaction"), Sheets("Donations"), "TPWF Annual Funds, Lone Star Land Steward", "A1: K"
FilterAndCopy Sheets("Donations by Transaction"), Sheets("SOTW"), "Stewards of the Wild", "A1: K"
FilterAndCopy Sheets("Donations by Transaction"), Sheets("Pivot Table"), "TPWF Annual Funds, Lone Star Land Steward", "A1: K"
Thanks!
Jordan
Bookmarks