I'm trying to copy the existing formulae in a selected range <
Range("A2:D2").Copy , then paste into the cells immediately below them for
a number of rows. (The row count varies, depending on my query results.)

Problem now, is that my sheet has grown to the point, that I get an error
saying the selection is too large to paste into. I have changed my code to
increment through the rows 1 row at a time, and populate the cells with my
formulae, but this has slowed me down a bit.

Any ideas?

Here's the code I started with for the copy and paste.

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A2:D2").Copy
'Range("A3:D" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False


--
D.S.

Try this:

With Range("A2:D2")
.AutoFill Range(.Cells, .Cells(1).End(xlDown)).Resize(, 4), _
Type:=xlFillDefault
End With


In article ,
"D.S." wrote:

I'm trying to copy the existing formulae in a selected range <
Range("A2:D2").Copy , then paste into the cells immediately below them for
a number of rows. (The row count varies, depending on my query results.)

Problem now, is that my sheet has grown to the point, that I get an error
saying the selection is too large to paste into. I have changed my code to
increment through the rows 1 row at a time, and populate the cells with my
formulae, but this has slowed me down a bit.

Any ideas?

Here's the code I started with for the copy and paste.

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A2:D2").Copy
'Range("A3:D" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

Thanks, but still get a run time error, < Selection too large

D.S.


"JE McGimpsey" wrote in message
...
Try this:

With Range("A2:D2")
.AutoFill Range(.Cells, .Cells(1).End(xlDown)).Resize(, 4), _
Type:=xlFillDefault
End With


In article ,
"D.S." wrote:

I'm trying to copy the existing formulae in a selected range <
Range("A2:D2").Copy , then paste into the cells immediately below them
for
a number of rows. (The row count varies, depending on my query
results.)

Problem now, is that my sheet has grown to the point, that I get an
error
saying the selection is too large to paste into. I have changed my code
to
increment through the rows 1 row at a time, and populate the cells with
my
formulae, but this has slowed me down a bit.

Any ideas?

Here's the code I started with for the copy and paste.

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A2:D2").Copy
'Range("A3:D" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

There's no need to do a selection with my code - as a quick fix, perhaps
you can select a single cell prior to running it. I just tested it on a
sheet with values in A2:A65535 and it worked fine.

Try eliminating the Selections from your code and using the range
objects directly. It makes your code smaller, faster, and, IMO, easier
to maintain.


In article ,
"D.S." wrote:

Thanks, but still get a run time error, < Selection too large

D.S.


"JE McGimpsey" wrote in message
...
Try this:

With Range("A2:D2")
.AutoFill Range(.Cells, .Cells(1).End(xlDown)).Resize(, 4), _
Type:=xlFillDefault
End With

Sorry, but I'm afraid you lost me. Can you give me a little more detail
please.

D.S.

"JE McGimpsey" wrote in message
...
There's no need to do a selection with my code - as a quick fix, perhaps
you can select a single cell prior to running it. I just tested it on a
sheet with values in A2:A65535 and it worked fine.

Try eliminating the Selections from your code and using the range
objects directly. It makes your code smaller, faster, and, IMO, easier
to maintain.


In article ,
"D.S." wrote:

Thanks, but still get a run time error, < Selection too large

D.S.


"JE McGimpsey" wrote in message
...
Try this:

With Range("A2:D2")
.AutoFill Range(.Cells, .Cells(1).End(xlDown)).Resize(, 4), _
Type:=xlFillDefault
End With

Selecting a range is almost never necessary. Unfortunately, it's how the
macro recorder does everything, so it tends to be the mode of choice for
beginning VBA programmers.

For instance, if I tried to record the process that you're doing, the
recorder would produce this (BTW - I didn't get any errors regarding the
paste range, so I'm not sure what's causing your problem):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/18/2004 by J.E.
'

'
Range("A2:D2").Select
Selection.Copy
Range("A2:D65536").Select
Selection.PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

this can be much more efficiently written by addressing the range
objects directly:

Public Sub Macro1()
Range("A2:D2").Copy Destination:= _
Range("A3:D65536")
End Sub

To look at your code:

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A2:D2").Copy
'Range("A3:D" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

You could write it without the selects as

Dim lngLastRow As Long
lngLastRow = Range("A2").End(xlDown).Row
Range("A2:D2").Copy
Range("A3:D" & lngLastRow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

In any case, the code I gave you earlier using Autofill doesn't depend
on Selections, so if you're getting an error that the paste area is too
large, it's at another point in your code.


In article ,
"D.S." wrote:

Sorry, but I'm afraid you lost me. Can you give me a little more detail
please.