ActiveSheet.Paste takes FOREVER!!

[mike_ward99]

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("A2M" + 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

 

[Joel]

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(SourceSheetName). _
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

 

[mike_ward99]

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(SourceSheetName). _
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









- Show quoted text -

I modified my code to

Range("A2").Select
Range(ActiveCell,ActiveCell.End(xlRight)).Copy Destination:=
Range("A2M" + 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?

 

[Joel]

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?

 

[mike_ward99]

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?









- Show quoted text -

Joel,

My VBA takes +- 20 minutes while a manual copy takes about 5 minutes...

 

[Joel]

I would turn on Learn Macro when you do the manual operation. Then put that
code into your VBA macro.

 

[Don Guillett]

I see lots of problems with your code but this will work from anywhere in
the workbook. Notice the placement of the dots ( . )

Sub copyinsheet()
lr = 7
With Sheets("Sheet6")
lc = .Cells(2, 1).End(xlToRight).column
..range(.Cells(2, 1), .Cells(2, lc)).AutoFill _
Destination:=.range(.Cells(2, 1), .Cells(lr, lc))
End With
End Sub

 

[Keith R]

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