Excel Macro Help Please

[nfrazer]

I'm having a brain drain - I'm trying to record a macro where I'd like to copy from one row (at the top of the spreadsheet) and paste the value on the next available row in the spreadsheet. I tried Ctrl End, Home, and then arrow down, but it then always goes to the same row and overwrites what was there. Thank you!!!!

 

[Nalani]

Hi, welcome to the board.

Have a look at the Offset Property in VBA Help file

 

[nfrazer]

Thanks Nalani!! To be honest, I'm pretty rusty with macros. I tried that and it's not doing the trick.

 

[GTO]

You are copying a cell from the top of the sheet and wish to plunk it into a cell farther below on the same sheet?

 

[Nalani]

This will copy A1:C1 and paste to the the first available row in column A.

Sub test()
    Dim LR As Long
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    ActiveSheet.Range("A1:C1").Copy ActiveSheet.Range("A" & LR)
End Sub

 

[nfrazer]

Yes GTO, that's what I'm doing.
Nalani, should I type what you've shown in your post through VBA (referencing the appropriate cell/row)?
N

 

[Nalani]

You can highlight and copy directly from this post and paste in a standard Module.

 

[nfrazer]

This is what I'm trying to do. My data is on row 5 and I'd like to copy it to the next available row. So it I have 10 rows of data (excluding row 5), I'd like to copy what's in row 5 onto row on row 16. The next time I run the macro, it would copy what's in row 5 (which will be different) onto row 17 and so on. Here's what I see in VBA. Really appreciate your help and patience as I haven't edited VBA much.

ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[nfrazer]

Nalani, I tried to copy what you gave me but it didn't paste as values so I was getting the wrong info because it copied the formulas down which referenced incorrect rows each time.

 

[Nalani]

Sub test()
    Dim LR As Long
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    ActiveSheet.Range("A5:C5").Copy ActiveSheet.Range("A" & LR)
End Sub

You don't say if it will be a range of cells on row 5.

The above will copy A5:C5 and place on the very first blank row from the bottom up,in column A.