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

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

"(E-Mail Removed)" 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("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
>
>

On Mar 12, 10:58 am, Joel <J...@discussions.microsoft.com> 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(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_war...@hotmail.com" 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("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- Hide quoted text -
>
> - 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?

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?

"(E-Mail Removed)" wrote:

> On Mar 12, 10:58 am, Joel <J...@discussions.microsoft.com> 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(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_war...@hotmail.com" 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("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- Hide quoted text -
> >
> > - 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?
>
>

On Mar 12, 1:01 pm, Joel <J...@discussions.microsoft.com> 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?
>
>
>
> "mike_war...@hotmail.com" wrote:
> > On Mar 12, 10:58 am, Joel <J...@discussions.microsoft.com> 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(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_war...@hotmail.com" 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("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- Hide quoted text -
>
> > > - 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?- 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.

"(E-Mail Removed)" wrote:

> On Mar 12, 1:01 pm, Joel <J...@discussions.microsoft.com> 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?
> >
> >
> >
> > "mike_war...@hotmail.com" wrote:
> > > On Mar 12, 10:58 am, Joel <J...@discussions.microsoft.com> 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(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_war...@hotmail.com" 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("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- Hide quoted text -
> >
> > > > - 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?- 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
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 12, 1:01 pm, Joel <J...@discussions.microsoft.com> 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?
>>
>>
>>
>> "mike_war...@hotmail.com" wrote:
>> > On Mar 12, 10:58 am, Joel <J...@discussions.microsoft.com> 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(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_war...@hotmail.com" 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("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- Hide quoted text -
>>
>> > > - 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?- Hide
>> > quoted text -
>>
>> - Show quoted text -
>
> Joel,
>
> My VBA takes +- 20 minutes while a manual copy takes about 5 minutes...
>