Results 1 to 8 of 8

Thread: Clear filter in Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clear filter in Macro

    HI Guys,

    I have this macro currently to open a closed workbook and copy certain columns to active workbook then close. I would like the opening of the new workbook not to show up, and it must update as I have it. Although my macro does not work if filters are In place, can anyone help me add in clear filters? I also realise there is probably a better code for the Copying of different cells.... But not great with VBA.


    Sub UpdateSheet()


    Dim x As Workbook



    Dim y As Workbook
    Set x = Workbooks.Open("C:\Users\611231350\Desktop\FNDDL MASTER.xlsx", UpdateLinks:=1)

    Set y = ThisWorkbook

    x.Sheets("MASTER").Range("A:B").Copy

    y.Sheets("DL MASTER").Range("A:B").PasteSpecial

    x.Sheets("MASTER").Range("D:O").Copy

    y.Sheets("DL MASTER").Range("D:O").PasteSpecialPaste:=xlPasteValues

    x.Sheets("MASTER").Range("P:Q").Copy

    y.Sheets("DL MASTER").Range("P:Q").PasteSpecial

    x.Sheets("MASTER").Range("R:AC").Copy

    y.Sheets("DL MASTER").Range("R:AC").PasteSpecialPaste:=xlPasteValues
    Application.CutCopyMode = False

    x.Close savechanges:=False



    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,211
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Clear filter in Macro

    How about
    Code:
    Sub UpdateSheet()
    Dim x As Workbook
    Dim y As Workbook
    Set x = Workbooks.Open("C:\Users\611231350\Desktop\FNDDL MASTER.xlsx", UpdateLinks:=1)
    
    Set y = ThisWorkbook
    With x.Sheets("Master")
       If .AutoFilterMode Then .AutoFilterMode = False
       .Range("A:B").Copy y.Sheets("DL master").Range("A1")
       .Range("D:Q").Copy y.Sheets("DL master").Range("D1")
       .Range("R:AC").Copy
       y.Sheets("DL master").Range("D1").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    
    x.Close savechanges:=False
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear filter in Macro

    I now have this Maco and have 2 issues that im hoping someone can help:

    1 - I need the 2nd workbook to open and update and all the values between the 2 workbooks (I.E Vlookups to update between the 2). At the moment the 2nd workbook opens, updates but doesn't seem to update the lookup values fast enough before the rest of the MAcro completes.
    2 - The Macro is very very slow and sometimes crashes my excel, any idea?

    Code:
    Sub UpdateSheet()
    
    
    
    
    
    Application.ScreenUpdating = False
    
    
    Application.DisplayStatusBar = False
    
    
    Application.EnableEvents = False
    
    
    ActiveSheet.DisplayPageBreaks = False
    
    
    
    
    
    Dim x As Workbook
    
    
    Dim y As Workbook
    
    
    Set x = Workbooks.Open("workbook address", True,True)
    
    
    Set y = ThisWorkbook
    
    
    
    
    
    x.Sheets("MASTER").AutoFilterMode = False
    
    
    y.Sheets("DL MASTER").AutoFilterMode = False
    
    
    
    
    
    x.Sheets("MASTER").Range("A:B").CopyDestination:=y.Sheets("DL MASTER").Range("A:B")
    
    
    x.Sheets("MASTER").Range("D:O").Value =x.Sheets("MASTER").Range("D:O").Value
    
    
    x.Sheets("MASTER").Range("P:Q").CopyDestination:=y.Sheets("DL MASTER").Range("P:Q")
    
    
    x.Sheets("MASTER").Range("R:AC").Value =y.Sheets("DL MASTER").Range("R:AC").Value
    
    
    
    
    
    y.Sheets("DL MASTER").Range("A1:AC1").AutoFilter
    
    
    
    
    
    y.Sheets.Application.CutCopyMode = False
    
    
    x.Close savechanges:=False
    
    
    
    
    
    Application.ScreenUpdating = True
    
    
    Application.DisplayStatusBar = True
    
    
    Application.EnableEvents = True
    
    
    ActiveSheet.DisplayPageBreaks = True
    
    
    
    
    
    
    
    
    MsgBox "Update Complete!"
    
    
    
    
    
    End Sub

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,211
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Clear filter in Macro

    Are these two lines correct
    Code:
    x.Sheets("MASTER").Range("A:B").CopyDestination:=y.Sheets("DL MASTER").Range("A:B")
    
    
    x.Sheets("MASTER").Range("D:O").Value =x.Sheets("MASTER").Range("D:O").Value
    
    
    x.Sheets("MASTER").Range("P:Q").CopyDestination:=y.Sheets("DL MASTER").Range("P:Q")
    
    
    x.Sheets("MASTER").Range("R:AC").Value =y.Sheets("DL MASTER").Range("R:AC").Value
    
    
    They are both updating workbook x rather than workbook y
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear filter in Macro

    Fluff,

    I was originally using the code below: I am coping columns from workbook X to Y (Either as copy or values). I Changed to the above code thinking it would speed it up....

    Thanks for the help

    Code:
    x.Sheets("PON CBT").Range("A:B").Copy
    y.Sheets("DL PON CBT").Range("A:B").PasteSpecial
    x.Sheets("PON CBT").Range("D:O").Copy
    y.Sheets("DL PON CBT").Range("D:O").PasteSpecial Paste:=xlPasteValues
    x.Sheets("PON CBT").Range("P:Q").Copy
    y.Sheets("DL PON CBT").Range("P:Q").PasteSpecial
    x.Sheets("PON CBT").Range("R:AC").Copy
    y.Sheets("DL PON CBT").Range("R:AC").PasteSpecial Paste:=xlPasteValues

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,211
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Clear filter in Macro

    Is there any particular column that will have data for every row?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear filter in Macro

    Quote Originally Posted by Fluff View Post
    Is there any particular column that will have data for every row?
    No every column will have a blank in it somewhere...

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,211
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Clear filter in Macro

    Ok, try
    Code:
    Sub UpdateSheet()
       Application.ScreenUpdating = False
       Application.DisplayStatusBar = False
       Application.EnableEvents = False
       ActiveSheet.DisplayPageBreaks = False
       Dim x As Workbook, y As Workbook
       Dim Usdrws As Long
       
       Set x = Workbooks.Open("workbook address", True, True)
       Set y = ThisWorkbook
       
       x.Sheets("MASTER").AutoFilterMode = False
       y.Sheets("DL MASTER").AutoFilterMode = False
       DoEvents
       With x.Sheets("MASTER")
          Usdrws = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
          .Range("A1:B" & Usdrws).Copy y.Sheets("DL MASTER").Range("A1")
          y.Sheets("DL MASTER").Range("D1:O" & Usdrws).Value = .Range("D1:O" & Usdrws).Value
          .Range("P1:Q" & Usdrws).Copy y.Sheets("DL MASTER").Range("P1")
          y.Sheets("DL MASTER").Range("R1:AC" & Usdrws).Value = .Range("R1:AC" & Usdrws).Value
          y.Sheets("DL MASTER").Range("A1:AC1").AutoFilter
       End With
       x.Close savechanges:=False
       Application.ScreenUpdating = True
       Application.DisplayStatusBar = True
       Application.EnableEvents = True
       ActiveSheet.DisplayPageBreaks = True
       MsgBox "Update Complete!"
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •