Clear filter in Macro

30136353

Board Regular
Joined
Aug 14, 2019
Messages
105
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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:
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub UpdateSheet()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.DisplayStatusBar = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.DisplayPageBreaks = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim x As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim y As Workbook[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set x = Workbooks.Open("workbook address", True,True)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set y = ThisWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").AutoFilterMode = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]y.Sheets("DL MASTER").AutoFilterMode = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").Range("A:B").CopyDestination:=y.Sheets("DL MASTER").Range("A:B")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").Range("D:O").Value =x.Sheets("MASTER").Range("D:O").Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").Range("P:Q").CopyDestination:=y.Sheets("DL MASTER").Range("P:Q")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").Range("R:AC").Value =y.Sheets("DL MASTER").Range("R:AC").Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]y.Sheets("DL MASTER").Range("A1:AC1").AutoFilter[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]y.Sheets.Application.CutCopyMode = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Close savechanges:=False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.DisplayStatusBar = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.DisplayPageBreaks = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]MsgBox "Update Complete!"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]
 
Upvote 0
Are these two lines correct
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").Range("A:B").CopyDestination:=y.Sheets("DL MASTER").Range("A:B")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#ff0000][FONT=Calibri][SIZE=3]x.Sheets("MASTER").Range("D:O").Value =x.Sheets("MASTER").Range("D:O").Value[/SIZE][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]x.Sheets("MASTER").Range("P:Q").CopyDestination:=y.Sheets("DL MASTER").Range("P:Q")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#ff0000][FONT=Calibri][SIZE=3]x.Sheets("MASTER").Range("R:AC").Value =y.Sheets("DL MASTER").Range("R:AC").Value[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]
[/SIZE][/FONT][/COLOR]
They are both updating workbook x rather than workbook y
 
Upvote 0
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
 
Upvote 0
Is there any particular column that will have data for every row?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top