Autofill Macro
Woohoo! I found a solution in another of your thread replies. The answer was
staring me in the face & I didn't see it.
Here's the solution based on your input:
Sub TestMacro()
Dim lastRow As Long
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
'Edit the "A" in the following line to match
'the column to test for last row
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:D3").Select
Selection.AutoFill Destination:=Range("B1:D" & lastRow),
Type:=xlFillDefault
Range("B1:D" & lastRow).Select
End Sub
--
Kat
"Kat" wrote:
My apologies OssieMac. I must be incredibly dense. When I tried your method,
here is what recorded:
Sub CopyDown()
'
' CopyDown Macro
' Macro recorded 10/6/2009
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End Sub
First: After I inserted the formulas I then select and copy the cells, then
I use SHIFT/CTRL DOWN-ARROW, and it goes all the way to the bottom of the
sheet instead of stopping next to the last row used. So this did not work.
Second: Although entire range is highlighted, when I select paste, only the
original selection of B1:D3 is pasted. The remainder are blank cells.
So this method isn't working for me. Am I missing something?
Using my original method gives me the following results:
Sub CopyDown2()
'
' CopyDown2 Macro
' Macro recorded 10/6/2009
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B1:D150")
Range("B1:D150").Select
End Sub
I am missing something. Please help.
--
Kat
|