Copy and paste data to next blank cell using command button click

[Dave Dunn]

I am using a Command Button that copies data from cell G3 to cell A3. I want to be able to copy the data to the next blank cell A4 to A14 in the column from cell G3. G3 data changes from a drop down list.

This is the code I have to do the first part which works as intended, but cannot figure how to paste data to the next blank cell. Your help would be greatly appreciated.

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

Dim xSheet As Worksheet

Set xSheet = ActiveSheet

If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then

xSheet.Range("G3").Copy

xSheet.Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End If


Application.ScreenUpdating = True


End Sub

 

[My Aswer Is This]

Try this:

Sub Copy_Paste_Me()
'Modified 11/8/2022  10:32:57 PM  EST
Application.ScreenUpdating = False
Dim sn As String
sn = ActiveSheet.Name
Dim Lastrow As Long
Lastrow = Sheets(sn).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(sn).Range("G3").Copy
If Lastrow < 3 Then Lastrow = 3
Sheets(sn).Cells(Lastrow, 1).PasteSpecial
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub