Consulting

Results 1 to 3 of 3

Thread: VBA macro for data filter and copy

  1. #1
    VBAX Regular
    Joined
    Feb 2017
    Posts
    21
    Location

    VBA macro for data filter and copy

    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

    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    634
    Location
    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

    マナ

  3. #3
    VBAX Regular
    Joined
    Feb 2017
    Posts
    21
    Location
    Thank you very much mana, your macro is just perfect.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •