Copying Data from Sheet and pasting based on Sheet name

[babar1988]

I have created a form on excel and feeding the data in the form it is transferring data based on the sheet name criteria i have given which is working fine.

Now, this practice makes my sheet very heavy so i have decided to name a simple sheet" Sheet 1" in which i have just fill the data and wanted to act same as my form do.

Now: I need to try copy data and paste on the sheet name criteria and on the last row of the table.

below is the code m using but it's not working properly

Sub CommandButton_Click()


Targetsheet = ThisWorkbook.Sheets(4).Range("B6").Value
If Targetsheet = "" Then



Exit Sub
End If

Dim A As Integer
Dim Tbl As ListObject
Dim LastRow2 As Long, LastRow3 As Long

'Set Tbl = TargetSheet.ListObjects("Table2")
Set Tbl = ThisWorkbook.Worksheets("E-Shots").ListObjects("Table2")

LastRow3 = Tbl.ListColumns(1).Range.Rows.Count '<-- last row in Column A in your Table
LastRow2 = Tbl.ListColumns(2).Range(LastRow3, 1).End(xlUp).Row '<-- last row with data in Column B in your Table

B = Worksheets("Sheet1").Range("B3").Value
Worksheets("E-Shots").Activate
'Lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.Cells(LastRow2 + 1, 4).Value = A
ActiveSheet.Cells(LastRow2 + 1, 3).Value = Range("b4").Value
ActiveSheet.Cells(LastRow2 + 1, 2).Value = Range("b5").Value

ActiveSheet.Cells(LastRow2 + 1, 5).Value = Range("b6").Value

A = ActiveSheet.Cells(LastRow2, 1).Value
ActiveSheet.Cells(LastRow2 + 1, 1).Value = A + 1

CreateObject("WScript.Shell").Popup "Your Data Has Been Successfully Updated", 1, "Hello!!"
End Sub


The code I am using for my form.

Private Sub CommandButton1_Click()


Targetsheet = ComboBox2.Value
If Targetsheet = "" Then
Exit Sub
End If

Dim A As Integer
Dim Tbl As ListObject
Dim LastRow2 As Long, LastRow3 As Long


Set Tbl = ThisWorkbook.Worksheets("E-Shots").ListObjects("Table2")

LastRow3 = Tbl.ListColumns(1).Range.Rows.Count '<-- last row in Column A in your Table
LastRow2 = Tbl.ListColumns(2).Range(LastRow3, 1).End(xlUp).Row '<-- last row with data in Column B in your Table


Worksheets(Targetsheet).Activate
'Lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
ActiveSheet.Cells(LastRow2 + 1, 4).Value = TextBox1.Value
ActiveSheet.Cells(LastRow2 + 1, 3).Value = ComboBox1.Value
ActiveSheet.Cells(LastRow2 + 1, 2).Value = TextBox3.Value

ActiveSheet.Cells(LastRow2 + 1, 5).Value = TextBox2.Value


A = ActiveSheet.Cells(LastRow2, 1).Value
ActiveSheet.Cells(LastRow2 + 1, 1).Value = A + 1

CreateObject("WScript.Shell").Popup "Your Data Has Been Successfully Updated", 1, "Hello!!"
End Sub
Private Sub CommandButton2_click()
Unload UserForm1

End Sub

Private Sub UserForm_Initialize()

With ComboBox2

.AddItem "First"
.AddItem "Second"
.AddItem "E-Shots"
End With


With ComboBox1

.AddItem "Mr."
.AddItem "Ms."
.AddItem "Mrs."
.AddItem "Dr."
.AddItem "Eng."
.AddItem "Chef."
End With


End Sub