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

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

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?

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?

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...

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...

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

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
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

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...