Paste and fill down

[Mugalh01]

Hiya,
I am a novice in VBA (this is day 2) and wanted to write a script to copy some values from sheet to another and fill down to the last filled row. This is what I have so far:

Dim lRow As Long


lRow = Worksheets("Data").Cells(Worksheets("Data").Rows.Count, "A").End(xlUp).Row


lRow = lRow + 1


Worksheets("Summary").Select
Range("A12:G19").Copy
Worksheets("Data").Select
Range("A" & lRow).PasteSpecial xlPasteValues
Worksheets("Summary").Select
Range("A4").Copy
Worksheets("Data").Range("H" & lRow).PasteSpecial xlPasteValues

Its the value in column H that I want to paste/fill down to the last filled row but I'm getting no joy.
Tried this - ActiveCell.AutoFill ActiveCell.Resize(Lastrow - ActiveCell.Row + 1) from a different thread as well as
ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(Cells(Rows.Count, "B").End(xlUp).Row, "C"))
but no joy - the last one partly worked but the number in column H was increasing incrementally. I wanted it to remain the same.

Help please

 

[emukiss10]

try this

Dim lastrowF As Long
Range("H2").Select
lastrowF = Range("A" & Rows.count).End(xlUp).row
ActiveCell.AutoFill Range(ActiveCell.Address, Cells(lastrowF, ActiveCell.Column))

 

[steve the fish]

Heres another way which should work for you:

Dim lRow As Long, rng As Range, rng2 As Range

Set rng = Sheets("Summary").Range("A12:G19")
Set rng2 = Sheets("Summary").Range("A4")

With Sheets("Data")
    lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Range(.Cells(lRow, "A"), .Cells(lRow + rng.Rows.Count - 1, "G")).Value = rng.Value
    .Range(.Cells(lRow, "H"), .Cells(lRow + rng.Rows.Count - 1, "H")).Value = rng2.Value
End With

 

[Mugalh01]

Thanks for your response.
However, I've tried the code and the autofill function is still not working. I'm not getting any errors, just not filling down. this is all of it compiled

Sub find_last_row()


Dim lRow As Long
Dim lastrowF As Long


lRow = Worksheets("Data").Cells(Worksheets("Data").Rows.Count, "A").End(xlUp).Row
lastrowF = Range("A" & Rows.Count).End(xlUp).Row
lRow = lRow + 1


Worksheets("Summary").Select
Range("A12:G19").Copy
Worksheets("Data").Select
Range("A" & lRow).PasteSpecial xlPasteValues
Worksheets("Summary").Select
Range("A4").Copy
Worksheets("Data").Range("H" & lRow).PasteSpecial xlPasteValues
Range("H2").Select
ActiveCell.AutoFill Range(ActiveCell.Address, Cells(lastrowF, ActiveCell.Column))


End Sub

 

[Mugalh01]

Works like a charm boss. I don't understand it yet (i'll try to decipher it) but it's all good, thank you.
On to the next obstacle

 

[Mugalh01]

ps. that was Steve's code that i was referring to.
don't know yet how to respond to individual responses