Clear filter in Macro

3

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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

K
  • Solved
VBA : Copy forn another worksheet only the new information.
Replies
6
Views
540
Katolux182
K
K
  • Question Question
VBA : Copy only new information from specific cells in another worksheet
Replies
6
Views
870
kevin9999
K
K
  • Question Question
Help with VB Code to copy workbook
Replies
0
Views
430
kizzie37
K
K
  • Solved
Finding next blank row before pasting data
Replies
4
Views
578
Fluff
Fluff
J
  • Question Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
470
joxyin
J

Forum statistics

Threads
1,223,608
Messages
6,173,325
Members
452,510
Latest member
RCan29

Share this page

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