 |
 | 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 / May 2008macro speeds
Under Pressure back online again looking for support. I have a small spreadsheet, 42 rows and 14 columns,with about 20 rows (195 cells in total) of conditional formats, 6 graphics,lots of text, numbers and formulae. This forms a template that I need to copy 300 times so that data from a second spreadsheet can populate it. I have written a macro to do this. Something must be wrong because it takes far too long - to even copy half a dozen or so. I can use my mouse to copy and paste it faster. I've done the usual things like turn the autocalculate off and screen update off. My macro copies the first 42 lines and then uses a simple loop to paste it in the appropriate place under the original 42 lines. Any suggestions? Post the code of your macro  SignatureKind regards, Niek Otten Microsoft MVP - Excel | Under Pressure back online again looking for support. | [quoted text clipped - 15 lines] | | Any suggestions? Thanks, Niek here it is !!!!!! Application.ScreenUpdating = False Sheets("Sheet1").Select Range("a1").Select Rows("1:42").Select Range("A42").Activate Selection.Copy i = o Do Until i = 3 i = i + 1 Range("a1").Select ActiveCell.Offset(42 * i, 0).Select ActiveSheet.Paste Range("A1").Select ActiveCell.Offset(42 * i, 0).Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell Loop > Post the code of your macro > [quoted text clipped - 17 lines] > | > | Any suggestions? try this in a REGULAR module. Notice the dots required for the WITH statement Sub copyit1() ActiveSheet.ResetAllPageBreaks With Sheets("sheet1") For i = 1 To 3 .Rows("1:42").Copy .Cells(i * 42, "a") ActiveWindow.SelectedSheets.HPageBreaks. _ Add Before:=.Cells(i * 42, "a") Next i End With End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks, Niek > [quoted text clipped - 47 lines] >> | >> | Any suggestions? Thanks Don I'd like to try this but not sure how to. Do I type it as a macro? What is the difference between the working of your code and my original? Cheers. Under Pressure > try this in a REGULAR module. Notice the dots required for the WITH > statement [quoted text clipped - 60 lines] > >> | > >> | Any suggestions? Please do not type it in. Copy from here and paste. Then fire from the active sheet. If all else fails, send your workbook to my address below. But, try first. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks Don > [quoted text clipped - 77 lines] >> >> | >> >> | Any suggestions? Thanks for the speedy response - much appreciated. Sorry to say that it didn't work - after a while got the message 'not respnding' and had to exit. Thanks for the offer of having a look at it, very kind of you. Best wishes Under Pressure > Please do not type it in. Copy from here and paste. Then fire from the > active sheet. [quoted text clipped - 81 lines] > >> >> | > >> >> | Any suggestions? Don I' ve done some more work on this problem. It's nothing to do with the macro. I've tried simple copy and paste - both copying the 42 rows and just the range a1:m42 - makes no difference - very slow indeed. In fact, once it copies about 60 times, it runs out of memory. I managed to save it after a copy of 30 and the file size was 24Gb. Any idea what's going on? Thanks again Under Pressure > Please do not type it in. Copy from here and paste. Then fire from the > active sheet. [quoted text clipped - 81 lines] > >> >> | > >> >> | Any suggestions? That form takes a ton of memory. The macro that I gave you would do it as desired but for that. I've been trying to tell you that it will NOT work that way. You must do as I suggested by having ONE form that you fill and copy to a database sheet and another macro to bring the data(s) back for work and printing. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Don > [quoted text clipped - 103 lines] >> >> >> | >> >> >> | Any suggestions? Don, thanks I think that one of your replies to me has gone missing. I don't remeber you suggesting "You must do as I suggested by having ONE form that you fill and copy to a database sheet and another macro to bring the data(s) back for work and printing." I'll have a go at this and if all else fails I can the work in batches of 30 - just means that I have to hang around while its doing it. Thanks again Under Pressure > That form takes a ton of memory. The macro that I gave you would do it as > desired but for that. I've been trying to tell you that it will NOT work [quoted text clipped - 109 lines] > >> >> >> | > >> >> >> | Any suggestions? Look at the private emails I sent to you. If you didn't get them, send me a private email and I will respond. What you are trying to do will not work and, even if it would, is a BAD idea. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Don, thanks > [quoted text clipped - 132 lines] >> >> >> >> | >> >> >> >> | Any suggestions? Your problem is the "i=o" - you've got the letter o and not the number 0. It could be treating i as a string, and appending 1 to the string, so after a few loops it will be o111111111111111111, and never 3. > Thanks, Niek > [quoted text clipped - 42 lines] > > | > > | Any suggestions? Sam Rather stupid of me, eh? However, it didn't help. Presumably it assumed that letter'o' had the value 0 as the macro is run. Hence setting variable I to zero. Macro is still incredibly slow - can't think why. It's only copying 42 lines. Even when I step through the macro one line at a time it pauses for ages on the paste line. Any more suggestions? Under Pressure > Your problem is the "i=o" - you've got the letter o and not the number 0. It > could be treating i as a string, and appending 1 to the string, so after a [quoted text clipped - 46 lines] > > > | > > > | Any suggestions? Niek Already posted the code. Want to look at the spreadsheet? If so, how do I post it? Thanks agaon Under Pressure > Post the code of your macro > [quoted text clipped - 17 lines] > | > | Any suggestions? |
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.