Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 4th, 2007, 09:13 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Speed up macro

I am trying to speed up this macro that I have. Maybe get rid of the Activation and Select. And any other methods to speed it up will be greatly appreciate. Here is the code in question:


Sub AllFolderFiles()

Application.DisplayAlerts = False
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String

MyPath = "C:\Documents and Settings\Owner\Desktop\upperdata300Loop"
ChDir MyPath
TheFile = Dir("*.xlsx")
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
wb.Activate
Range("a1").CurrentRegion.Copy
wb.Close
Range("a2").Select
ActiveSheet.Paste
ActiveSheet.Range("$A$1:$PG$1643").AutoFilter Field:=6, Criteria1:="1"
Range("a1").CurrentRegion.Copy
Windows("results.xlsx").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Windows("UpperCompare 001.xlsx").Activate
ActiveSheet.Range("$F$1:$ATG$500").AutoFilter Field:=6
Range("A2:D400").Select
Selection.ClearContents

TheFile = Dir
Loop
End Sub


  #2 (permalink)  
Old April 30th, 2007, 11:39 AM
Registered User
 
Join Date: Dec 2006
Location: , , Thailand.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Eric

Try to place this code under macro name

Application.ScreenUpdating = False


And place this code before End sub

Code:
Application.ScreenUpdating = true
Change these 3 line below

Code:
Range("a1").CurrentRegion.Copy
Windows("results.xlsx").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
to
Code:
Range("a1").CurrentRegion.Copy
Application.goto Workbooks("results.xlsx").Sheet("YourSheetName").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).

Range("a1").CurrentRegion.Copy
Application.goto Workbooks("results.xlsx").Sheets("YourSheetName"). Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).

I do not sure that

Code:
Range("a1").CurrentRegion.Copy Workbooks("results.xlsx").Sheets("YourSheetName").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).
Will work but please try on.

Change thess 2 lines Code
Code:
Range("A2:D400").Select
Selection.ClearContents
to
Code:
Range("A2:D400").ClearContents
N. Yauvasuta


Similar Threads
Thread Thread Starter Forum Replies Last Post
calling to xlam macro from macro inside xlsb SteveB Excel VBA 0 June 30th, 2008 06:43 PM
Speed up macro - deleting rows icabo Excel VBA 1 October 8th, 2007 02:18 PM
Failure to speed up macro jeroen999 Excel VBA 1 May 16th, 2006 05:39 AM
speed up macro Pindacko Excel VBA 1 April 27th, 2006 01:41 PM
speed up macro Excel VBA crmpicco Excel VBA 8 December 21st, 2005 11:17 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.