Results 1 to 3 of 3

Thread: .autofilter to certain column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    193
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default .autofilter to certain column

    G'day guys.

    Have the below code:

    Code:
    Set TB = ActiveWorkbookWorkbooks.Open "C:\Users\Luke\Desktop\LIVE\Best Shed Scheduler Flashing.xlsm"
    Set OB = ActiveWorkbook
    erow = OB.Sheets("Corner Flashing Machine Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    TB.Activate
    Sheets("Corner").Range("H:Y").EntireColumn.Hidden = False
     With Sheets("Corner").UsedRange
                .AutoFilter
                .AutoFilter Field:=23, Criteria1:="Corner"
                .AutoFilter Field:=25, Criteria1:="JOB SCHEDULED"
                .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy OB.Sheets("Corner Flashing Machine Data").Cells(erow, 1)
                .AutoFilter
        End With
    Sheets("Corner").Range("I:U").EntireColumn.Hidden = True
    Sheets("Corner").Range("V:Y").EntireColumn.Hidden = False
    
    
    OB.Save
    OB.Close
    Works fine as intended HOWEVER it's grabbing the entire row which has been fine up until recently. Now we wish to keep the same criteria but only copying from A:H and then pasting in the destination OB.Sheets("Corner Flashing Machine Data").Cells(erow, 1).

    Any ideas?

    Cheers!

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,230
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: .autofilter to certain column

    Untested....

    Code:
        Set TB = ActiveWorkbook
        Workbooks.Open "C:\Users\Luke\Desktop\LIVE\Best Shed Scheduler Flashing.xlsm"
        Set OB = ActiveWorkbook
        erow = OB.Sheets("Corner Flashing Machine Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        TB.Activate
        Sheets("Corner").Range("H:Y").EntireColumn.Hidden = False
        With Sheets("Corner").UsedRange
            .AutoFilter
            .AutoFilter Field:=23, Criteria1:="Corner"
            .AutoFilter Field:=25, Criteria1:="JOB SCHEDULED"
            Intersect(.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow, Sheets("Corner").Columns("A:H")).Copy OB.Sheets("Corner Flashing Machine Data").Cells(erow, 1)
            .AutoFilter
        End With
        Sheets("Corner").Range("I:U").EntireColumn.Hidden = True
        Sheets("Corner").Range("V:Y").EntireColumn.Hidden = False
    
    
        OB.Save
        OB.Close
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    193
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: .autofilter to certain column

    Quote Originally Posted by MARK858 View Post
    Untested....

    Code:
        Set TB = ActiveWorkbook
        Workbooks.Open "C:\Users\Luke\Desktop\LIVE\Best Shed Scheduler Flashing.xlsm"
        Set OB = ActiveWorkbook
        erow = OB.Sheets("Corner Flashing Machine Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        TB.Activate
        Sheets("Corner").Range("H:Y").EntireColumn.Hidden = False
        With Sheets("Corner").UsedRange
            .AutoFilter
            .AutoFilter Field:=23, Criteria1:="Corner"
            .AutoFilter Field:=25, Criteria1:="JOB SCHEDULED"
            Intersect(.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow, Sheets("Corner").Columns("A:H")).Copy OB.Sheets("Corner Flashing Machine Data").Cells(erow, 1)
            .AutoFilter
        End With
        Sheets("Corner").Range("I:U").EntireColumn.Hidden = True
        Sheets("Corner").Range("V:Y").EntireColumn.Hidden = False
    
    
        OB.Save
        OB.Close
    Works as intended however 1 little thing quite weird actually there are some gaps in column H which meets the criteria as the data is sending to the page. Weird.

    See below.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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