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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
547
Katolux182
K
K
  • Question Question
VBA : Copy only new information from specific cells in another worksheet
Replies
6
Views
901
kevin9999
K
K
  • Question Question
Help with VB Code to copy workbook
Replies
0
Views
453
kizzie37
K
K
  • Solved
Finding next blank row before pasting data
Replies
4
Views
580
Fluff
Fluff
J
  • Question Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
488
joxyin
J

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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