|
ActiveSheet.Paste takes FOREVER!!
Hi,
I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike |
ActiveSheet.Paste takes FOREVER!!
It looks like you are copying all 65,000 rows of data??? for copying you can use a single command and specifiy destination Workbooks(SourceFilename).Worksheets(SourceSheetNa me). _ Range(Cells(1, 1), Cells(MaxRows, MaxColumns)).Copy _ Destination:=Workbooks(ThisWorkbook.Name). _ Worksheets(DestSheetName).Range("A1") try something like this Lastcolumn = Range(ActiveCell,ActiveCell.End(xlRight)).column Lastrow = Range(ActiveCell,ActiveCell.End(xldown)).row Range(cells(1,2),cells(Lastrow,lastcolumn)).Copy " wrote: Hi, I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike |
ActiveSheet.Paste takes FOREVER!!
On Mar 12, 10:58 am, Joel wrote:
It looks like you are copying all 65,000 rows of data??? for copying you can use a single command and specifiy destination Workbooks(SourceFilename).Worksheets(SourceSheetNa me). _ Range(Cells(1, 1), Cells(MaxRows, MaxColumns)).Copy _ Destination:=Workbooks(ThisWorkbook.Name). _ Worksheets(DestSheetName).Range("A1") try something like this Lastcolumn = Range(ActiveCell,ActiveCell.End(xlRight)).column Lastrow = Range(ActiveCell,ActiveCell.End(xldown)).row Range(cells(1,2),cells(Lastrow,lastcolumn)).Copy " wrote: Hi, I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike- Hide quoted text - - Show quoted text - I modified my code to Range("A2").Select Range(ActiveCell,ActiveCell.End(xlRight)).Copy Destination:= Range("A2:DM" + intRowCount). but there doesn't seem to be a siginificant improvement :-( Instead of doing the copy process in one go, would it be better looping through the copy process one row at a time? Any comments? |
ActiveSheet.Paste takes FOREVER!!
Looping I think is slower. I sometimes do it with using offsets. The macro
shouldn't take any longer than doing it manually. Have you tried to highlight the cell manually then do a copy and paste and see how long that takes? " wrote: On Mar 12, 10:58 am, Joel wrote: It looks like you are copying all 65,000 rows of data??? for copying you can use a single command and specifiy destination Workbooks(SourceFilename).Worksheets(SourceSheetNa me). _ Range(Cells(1, 1), Cells(MaxRows, MaxColumns)).Copy _ Destination:=Workbooks(ThisWorkbook.Name). _ Worksheets(DestSheetName).Range("A1") try something like this Lastcolumn = Range(ActiveCell,ActiveCell.End(xlRight)).column Lastrow = Range(ActiveCell,ActiveCell.End(xldown)).row Range(cells(1,2),cells(Lastrow,lastcolumn)).Copy " wrote: Hi, I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike- Hide quoted text - - Show quoted text - I modified my code to Range("A2").Select Range(ActiveCell,ActiveCell.End(xlRight)).Copy Destination:= Range("A2:DM" + intRowCount). but there doesn't seem to be a siginificant improvement :-( Instead of doing the copy process in one go, would it be better looping through the copy process one row at a time? Any comments? |
ActiveSheet.Paste takes FOREVER!!
On Mar 12, 1:01 pm, Joel wrote:
Looping I think is slower. I sometimes do it with using offsets. The macro shouldn't take any longer than doing it manually. Have you tried to highlight the cell manually then do a copy and paste and see how long that takes? " wrote: On Mar 12, 10:58 am, Joel wrote: It looks like you are copying all 65,000 rows of data??? for copying you can use a single command and specifiy destination Workbooks(SourceFilename).Worksheets(SourceSheetNa me). _ Range(Cells(1, 1), Cells(MaxRows, MaxColumns)).Copy _ Destination:=Workbooks(ThisWorkbook.Name). _ Worksheets(DestSheetName).Range("A1") try something like this Lastcolumn = Range(ActiveCell,ActiveCell.End(xlRight)).column Lastrow = Range(ActiveCell,ActiveCell.End(xldown)).row Range(cells(1,2),cells(Lastrow,lastcolumn)).Copy " wrote: Hi, I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike- Hide quoted text - - Show quoted text - I modified my code to Range("A2").Select Range(ActiveCell,ActiveCell.End(xlRight)).Copy Destination:= Range("A2:DM" + intRowCount). but there doesn't seem to be a siginificant improvement :-( Instead of doing the copy process in one go, would it be better looping through the copy process one row at a time? Any comments?- Hide quoted text - - Show quoted text - Joel, My VBA takes +- 20 minutes while a manual copy takes about 5 minutes... |
ActiveSheet.Paste takes FOREVER!!
I would turn on Learn Macro when you do the manual operation. Then put that
code into your VBA macro. " wrote: On Mar 12, 1:01 pm, Joel wrote: Looping I think is slower. I sometimes do it with using offsets. The macro shouldn't take any longer than doing it manually. Have you tried to highlight the cell manually then do a copy and paste and see how long that takes? " wrote: On Mar 12, 10:58 am, Joel wrote: It looks like you are copying all 65,000 rows of data??? for copying you can use a single command and specifiy destination Workbooks(SourceFilename).Worksheets(SourceSheetNa me). _ Range(Cells(1, 1), Cells(MaxRows, MaxColumns)).Copy _ Destination:=Workbooks(ThisWorkbook.Name). _ Worksheets(DestSheetName).Range("A1") try something like this Lastcolumn = Range(ActiveCell,ActiveCell.End(xlRight)).column Lastrow = Range(ActiveCell,ActiveCell.End(xldown)).row Range(cells(1,2),cells(Lastrow,lastcolumn)).Copy " wrote: Hi, I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike- Hide quoted text - - Show quoted text - I modified my code to Range("A2").Select Range(ActiveCell,ActiveCell.End(xlRight)).Copy Destination:= Range("A2:DM" + intRowCount). but there doesn't seem to be a siginificant improvement :-( Instead of doing the copy process in one go, would it be better looping through the copy process one row at a time? Any comments?- Hide quoted text - - Show quoted text - Joel, My VBA takes +- 20 minutes while a manual copy takes about 5 minutes... |
ActiveSheet.Paste takes FOREVER!!
I've seen workbooks that can take over 30 minutes with any update- not just
copy/paste, but even changing cell values- generally, because there are a lot of complex formula (Vlookups, etc.) in the workbook that keep recalculating. It seems to be worse if the formulas are all inter-related, (change Formula1 which changes Formula2; I'm guessing that because Formula2 changed a workbook cell value, Formula1 has to be recalculated even though it hasn't changed, and so on). One possible test that comes to mind is to hit F9 ("calc now") and see how long it takes to recalculate everything in your book. However, I don't know if that is a comprehensive test especially if cell values themselves haven't changed- I don't know how Excel decides what to calculate. In any event, if you have a more than a nominal number of formulas in your workbook, try adding some vba code to turn off calculation before your operation, then back on after. If you are pasting to a worksheet that is the source for /any/ of your formulas you will still have to recalculate anyway, so don't forget to do that at the end. airball code: Sub pasteMyStuff Application.Calculate = XLManual 'Joel's code to paste in one action Application.Calculate = XLAutomatic 'possibly force a calculation here if needed End Sub wrote in message ups.com... On Mar 12, 1:01 pm, Joel wrote: Looping I think is slower. I sometimes do it with using offsets. The macro shouldn't take any longer than doing it manually. Have you tried to highlight the cell manually then do a copy and paste and see how long that takes? " wrote: On Mar 12, 10:58 am, Joel wrote: It looks like you are copying all 65,000 rows of data??? for copying you can use a single command and specifiy destination Workbooks(SourceFilename).Worksheets(SourceSheetNa me). _ Range(Cells(1, 1), Cells(MaxRows, MaxColumns)).Copy _ Destination:=Workbooks(ThisWorkbook.Name). _ Worksheets(DestSheetName).Range("A1") try something like this Lastcolumn = Range(ActiveCell,ActiveCell.End(xlRight)).column Lastrow = Range(ActiveCell,ActiveCell.End(xldown)).row Range(cells(1,2),cells(Lastrow,lastcolumn)).Copy " wrote: Hi, I need booting into a better direction... I have several sheets containing formulas in Row 2 (sometimes from columns A to DM..) which need copying down a large (sometimes 2000) numer of rows. I use something along the lines of... Worksheets("X").Activate Worksheets("X").Select Ranhe("A2").Select Rnage(ActiveCell,ActiveCell.End(xlRight)).Copy Range("A2:DM" + intRowCount).Select ActiveSheet.Paste ...at which stage the processor fan goes a million rpm, and I fall asleep waiting for something to happen. The normal speed-enhancing settings are correct( e.g. calculate is xlManual, screenupdating is false, etc.). Any significant, problem recommendations will warrant hero-worship. Thanks Mike- Hide quoted text - - Show quoted text - I modified my code to Range("A2").Select Range(ActiveCell,ActiveCell.End(xlRight)).Copy Destination:= Range("A2:DM" + intRowCount). but there doesn't seem to be a siginificant improvement :-( Instead of doing the copy process in one go, would it be better looping through the copy process one row at a time? Any comments?- Hide quoted text - - Show quoted text - Joel, My VBA takes +- 20 minutes while a manual copy takes about 5 minutes... |
| All times are GMT +1. The time now is 03:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com