Copy & paste to empty row in seperate workbook

[mattyads05]

Hi all.

Long time listener, 1st time caller.
I am still learning a lot every day with VBA, having only recently returning to a position that requires me to use it again, and I didnt know much before!

I have a UserForm which is populating data entered into 'row 2' on 'sheet 1' which I need to be copied and pasted into the next blank row on 'sheet 1' in a different workbook.

I have managed to get the userform to submit the data by recording a macro of me selecting a row and then opening the new workbook and pasting it, but I cannot find the piece of code that will select an empty row, at least that I can get to work.

I will post what I have below, can anyone help at all please?

Private Sub CommandButton1_Click()
Rows("2:2").Select
Selection.Copy
ChDir "G:\Shortcuts\RCC\TRAINING SERVICES\Training & Quality\Training WIP\Matt"
Workbooks.Open Filename:= _
"G:\Shortcuts\RCC\TRAINING SERVICES\Training & Quality\Training WIP\Matt\Feedback results.xlsm"
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
End Sub

 

[Takae]

There is no error processing.
Hope this helps.

Private Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook
Dim LR As Long

Set wb1 = ThisWorkbook
 wsb1.Sheets("Sheet1").Rows("2:2").Copy
 
Workbooks.Open Filename:= _
"G:\Shortcuts\RCC\TRAINING SERVICES\Training & Quality\Training WIP\Matt\Feedback results.xlsm"
Set wb2 = ActiveWorkbook
LR = wb2.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
 wb2.Sheets("Sheet1").Rows(LR).Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 wb1.Sheets("sheet1").Activate
 Range("A3").Select
 End Sub

 

[mattyads05]

Thanks for getting back to me.

I am getting an error message for this part of the code you gave me.

Not sure what it is doing now, so I have no idea how to fix it.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
wb1.Sheets("sheet1").Activate
Range("A3").Select
End Sub

 

[Takae]

wsb1.Sheets("Sheet1").Rows("2:2").Copy

Sorry,this is not wsb1, correct variable is wb1.
Please try again.

 

[mattyads05]

Thanks. I made the correction. I am getting a debug on the following line of code

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I am not sure what could be going wrong here, any ideas?

Thank you for your help, I really do appreciate it.

 

[Takae]

I didn't get any errors...
Please try this new one.

Private Sub CommandButton1_Click()Dim wb1 As Workbook, wb2 As Workbook
Dim LR As Long


Set wb1 = ThisWorkbook
 
Workbooks.Open Filename:= _
"G:\Shortcuts\RCC\TRAINING SERVICES\Training & Quality\Training WIP\Matt\Feedback results.xlsm"
Set wb2 = ActiveWorkbook
LR = wb2.Sheets("sheet1").Cells(Rows.count, 1).End(xlUp).row + 1
 wb2.Sheets("Sheet1").Rows(LR).Value = wb1.Sheets("Sheet1").Rows("2:2").Value
 wb1.Sheets("sheet1").Activate
 Range("A3").Select
 End Sub

 

[mattyads05]

Yes that worked perfectly that time. Thank you again