Excel VBA to paste to new line after entry

[jfrancis]

I'm trying to make a sales quotation entry process simpler by using macros. This code below works, but it goes horizontally across the sheet instead of going vertically down the columns.

I have dynamic data (from vlookup) generated in cell G3 and I've made a button to copy this data from G3 and paste the value to column A1. As of now, upon clicking the assigned macro button, it pastes data from, G3 to H3, I3, J3, K3 and so on. I need the macro to copy-paste from G3 to the cell directly below, like A1, A2, A3, A4 and so on...

Sub paste2()
' paste2 Macro
    Dim lastCol As Long
' this finds the number of the last column
lastCol = Cells(3, Columns.Count).End(xlToLeft).Column


   Range("G3").Copy


Range("A1").Select
   Cells(3, lastCol + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

 

[sericom]

Is this what you're after

Sub paste2()
' paste2 Macro
    Dim lastRow As Long
' this finds the number of the last row
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("G3").Copy
    Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[jfrancis]

Is this what you're after

Sub paste2()
' paste2 Macro
    Dim lastRow As Long
' this finds the number of the last row
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("G3").Copy
    Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Thank you sericom! This was exactly what I was looking for! Saved me a lot of web searches...

 

[MARK858]

Slightly shorter version without the copy/paste...

Sub paste2()
    Range("A" & Rows.Count).End(xlUp)(2).Value = Range("G3").Value
End Sub

 

[jfrancis]

Thank you MARK858. I needed the data to be pasted in column D as well and your formula helped me to do that. VB is pretty interesting. I'm currently learning C programming though

 

[MARK858]

You're welcome (btw it is not a formula, it is VBA code/script, a formula is what you put in a cell with a =/+ sign at the start or within a code with a .Formula or .FormulaR1C1 and a = in front)

 

[daniboymu]

Is this what you're after

Sub paste2()
' paste2 Macro
    Dim lastRow As Long
' this finds the number of the last row
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("G3").Copy
    Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

hey, could you make it so instead of being the last row, could be an especific row, please!!!

 

[daniboymu]

hey, could you make it so instead of being the last row, could be at 7:7 row, please!!!

 

[MARK858]

Rows(7).Value = Rows(1).Value

 

[daniboymu]

Rows(7).Value = Rows(1).Value