geomano
11-13-2017, 01:00 AM
Hello, I am trying to write a macro, which will loop through all headers in a specific sheet and in case last 12 characters are "_TEAM_OFFICE", it will copy/paste the filtered results into another sheet. Same procedure will go for all other columns in range. Unfortunately, my code doesn't seem to be working properly. I'd do appreciate some help. Thanks. Attached the raw data spreadsheet.
Sub Copydata()
Dim erow As Long
Sheets("Raw Data").Select
Set MR = Sheets("Raw Data").Range("A1:zz1")
For Each cell In MR
erow = Sheet3.Range("a" & Rows.Count).End(xlUp).Row + 1
If Right(cell.Value, 12) = "_TEAM_OFFICE" Then cell.AutoFilter Field:=1, Criteria:="UB"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet3.Range("a" & erow)
Next
End Sub
Sub Copydata()
Dim erow As Long
Sheets("Raw Data").Select
Set MR = Sheets("Raw Data").Range("A1:zz1")
For Each cell In MR
erow = Sheet3.Range("a" & Rows.Count).End(xlUp).Row + 1
If Right(cell.Value, 12) = "_TEAM_OFFICE" Then cell.AutoFilter Field:=1, Criteria:="UB"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet3.Range("a" & erow)
Next
End Sub