Sleeper: Copy cell down

[psauber]

I need to copy the contents of Row 1 Column X and Column XX
down by 5 or 6 lines depending on how many rows there are.
Example is attached since it doesn't format properly typing it
here

Contents of columns D & E need to be copied down into 5 cells.
However, sometimes columns D & E could be E & F or C & D
Thanks for any help

[Bob Phillips]

I need to copy the contents of Row 1 Column X and Column XX
down by 5 or 6 lines depending on how many rows there are.
Example is attached since it doesn't format properly typing it
here

Contents of columns D & E need to be copied down into 5 cells.
However, sometimes columns D & E could be E & F or C & D
Thanks for any help

You don't say how you will know which columns to copy down, so I have assumed you will activate either of the cells, D2 or E2 in your example.



Sub CopyDown()
Dim rng As Range
With ActiveCell
If .Offset(1, 1).Value = "" Then
Set rng = Cells(2, .Column).Resize(, 2)
Else
Set rng = Cells(2, .Column).Offset(0, -1).Resize(, 2)
End If
End With
rng.AutoFill rng.Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1)
End Sub

[psauber]

Hi Xld

I don't know what columns they will be but they will always be those
two but their position can change. However the column headers will
never change. They are Country and Number and their values are
the ones that need to be copied down but not incrementally. Perhaps
we can specify those headers as criteria ?
Also I don't know whether the number of rows to be copied is 5 or 6.
That can change as well.

Thanks
Peter

[Bob Phillips]

Hi Xld

I don't know what columns they will be but they will always be those
two but their position can change. However the column headers will
never change. They are Country and Number and their values are
the ones that need to be copied down but not incrementally. Perhaps
we can specify those headers as criteria ?
Also I don't know whether the number of rows to be copied is 5 or 6.
That can change as well.

I already catered for variable rows. This should now do it regardless



Sub CopyDown()
Dim iCol As Long
Dim rng As Range
On Error Resume Next
iCol = Application.Match("Country", Rows("1:1"), 0)
On Error GoTo 0
If iCol > 0 Then
Set rng = Cells(2, iCol).Resize(, 2)
rng.AutoFill rng.Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1)
End If
End Sub

[psauber]

Hello Xld

It works fine except that the copied cells are incremental
where they should only be a copy of the original cell.

How you managed to achieve this with only a few lines
of code is very impressive.

Peter

[Bob Phillips]

Hello Xld

It works fine except that the copied cells are incremental
where they should only be a copy of the original cell.

How you managed to achieve this with only a few lines
of code is very impressive.

Peter,

Are you saying they ARE incremental, or they SHOULD be. In my test, it copies the exact values. So the 4523 is copied down to D7, the 7856 is copied down to E6.

[psauber]

Xld

Yes, it should have been copy but it was incremental. I came
across by chance the switch XfillCopy which I put at the end
of the AutoFill line and it worked. All is OK
Thanks for your quick replies.
Peter

[Bob Phillips]

Xld

Yes, it should have been copy but it was incremental. I came
across by chance the switch XfillCopy which I put at the end
of the AutoFill line and it worked. All is OK
Thanks for your quick replies.
Peter

I wonder why yours behaved differently thanh mine. What Xl version have you?

[psauber]

Hi Xld

Sorry for the delay

Version 2002 SP 2

However, it does say somewhere that the
default is incremental and to get it to copy
requires that additional switch

Peter

[Bob Phillips]

However, it does say somewhere that the
default is incremental and to get it to copy
requires that additional switch
Hi Peter,

In XL2000, it says, and I quote

If this argument is xlFillDefault or omitted, Microsoft Excel selects the most appropriate fill type, based on the source range.