PDA

View Full Version : [SOLVED] VBA macro for data filter and copy



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

mana
11-13-2017, 03:52 AM
Option Explicit


Sub test()
Dim tbl As Range
Dim c As Range

Set tbl = Sheets("Sheet1").Range("a1").CurrentRegion

For Each c In tbl.Rows(1).Cells
If c.Value Like "*_TEAM_OFFICE" Then
tbl.AutoFilter
tbl.AutoFilter c.Column, "UB"
If tbl.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
tbl.Offset(1).Copy Sheets("Sheet3").Range("a" & Rows.Count).End(xlUp).Offset(1)
End If
tbl.AutoFilter
End If
Next

End Sub



マナ

geomano
11-13-2017, 04:04 AM
Thank you very much mana, your macro is just perfect.