Consulting

Results 1 to 3 of 3

Thread: VBA macro for data filter and copy

  1. #1

    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.
    [COLOR=#333333]Sub Copydata()[/COLOR] 
     
    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 
     
    [COLOR=#333333]End Sub[/COLOR][COLOR=#333333][/COLOR] 
    
    
    Formatting tags added by mark007

    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    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 
    
    
    Formatting tags added by mark007

    マナ

  3. #3
    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
  •