 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Speed the macro works
I am relatively new to macro writing. I have written a macro that copies 30 lines of a spreadsheet ( this is a template for student report ) 300 times into the rows below it. Works fine at first, quite fast as one wouls expect. However, as more sections of the 30 lines are copied, it goes slower and slower and takes the best part of 20mins to complete. Any suggestions? Under Pressure Are there lots of formulas in those 30 lines? If so, try this before your code: Application.Calculation = xlCalculationManual Application.ScreenUpdating = FALSE and this after: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Darren > I am relatively new to macro writing. I have written a macro that copies 30 > lines of a spreadsheet ( this is a template for student report ) 300 times [quoted text clipped - 3 lines] > > Under Pressure Thanks Darren No calculations - just lots of coditional formatting. I'll try your idea. Cheers Under Pressure > Are there lots of formulas in those 30 lines? > If so, try this before your code: [quoted text clipped - 14 lines] > > > > Under Pressure Hi Darren, I tried this for my code which used to take 30+ minutes to execute. Guess what, it takes less than 2 minutes now!!! Thank you so much. Regards Rajesh > Are there lots of formulas in those 30 lines? > If so, try this before your code: [quoted text clipped - 14 lines] > > > > Under Pressure The first thing is to make sure calculation mode is manual, at least while the macro is running. Also it may not be necessary for the macro to do more than one copy/paste, depending on the layout of your data. A simplified example: Range("A1:B3").AutoFill Range("A1:B99"), xlFillCopy Here, there is data in the first two rows of the source range, A1:B2, and row 3 is blank. I want to copy down the pattern of 2 data rows and 1 blank row multiple times. Rather than doing a loop the above does it all at once.  SignatureJim >I am relatively new to macro writing. I have written a macro that copies >30 [quoted text clipped - 5 lines] > > Under Pressure Jim Told you I was new to this macro writing. This is a good idea that I will try. You want to see the macro that I've written to copy each row of a 300 line spreadsheet into this template!!! Any suggestions as to where I might get a set of useful commands - I've been learning his macro stuff by using key strokes and then looking at the generated code. Thanks again Under Pressure > The first thing is to make sure calculation mode is manual, at least while > the macro is running. [quoted text clipped - 17 lines] > > > > Under Pressure Jim I've now tried your idea. Works OK but doesn't copy the formats of each row. Some of the 31 rows that are being copied have different heights because some of the cells contain a logo, text boxes while others contain numbers that have conditional formats in them. I've tried adapting the Range("a1:b3") at the beginning to Rows("1:3") but this doesn't work. As for turning the autocalculate off, a marginal increase in speed was detected. Thanks again Under Pressure > The first thing is to make sure calculation mode is manual, at least while > the macro is running. [quoted text clipped - 17 lines] > > > > Under Pressure People might be able to help better if you post your code. But my suggestion would be to break it into two steps: 1. use the autofill method suggested by Jim 2. then copy the formats and conditionalformats from the original range to the whole range. Darren > Jim > [quoted text clipped - 34 lines] >>> >>> Under Pressure Well I find that filling copies everything a normal copy does. As for row heights this works for me: Range("1:3").AutoFill Range("1:99"), xlFillCopy SignatureJim > Jim > [quoted text clipped - 41 lines] >> > >> > Under Pressure Are you using something similar to this: Sheets("Sheet1").Select Range("A1").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste If so, this type of code may have a lot of overhead that ties up too much memory. Instead give something like this a shot: Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A1").Value Just a thought... Mark Ivey > I am relatively new to macro writing. I have written a macro that copies > 30 [quoted text clipped - 5 lines] > > Under Pressure |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.