CODEWITHSUNDEEP
excel-2010
Julianne Ferrer
Julianne Ferrer

Reputation: 11

excel data entry overwrite first save problem

I want my entry data to be save continuously to last save cell this code overwrites the first save this is the code for my save written in vba module(macro) Sub Transfer()

Dim objList As ListObject ' ListObject to refer the Table created for Employee
Dim shForm As Worksheet 'Worksheet variable to refer Form

Set objList = ThisWorkbook.Sheets("KIT_ASSY").ListObjects("EmpTable") ' Assigning Employee Table

Set shForm = ThisWorkbook.Sheets("EGS DB") ' Assigning Form sheet to this variable

If objList.DataBodyRange(1, 1).Value <> "" Then ' Identifying whether row 1 is blank in current table or not

   objList.ListRows.add Position:=1 'If row 1 is not blank in table then insert a new blank for at 1
    
End If


'Referring ObjList and transferring data to DataBodyRange of Table

With objList

  .DataBodyRange(1, 1).Value = shForm.Range("h3").Value 'Reference #
    .DataBodyRange(1, 2).Value = shForm.Range("b3").Value 'Reference #
.DataBodyRange(1, 3).Value = shForm.Range("c8").Value 'Reference #
.DataBodyRange(1, 4).Value = shForm.Range("d8").Value 'Reference #
.DataBodyRange(1, 5).Value = shForm.Range("g8").Value 'Reference #
   
  .DataBodyRange(2, 1).Value = shForm.Range("h3").Value 'Reference #
    .DataBodyRange(2, 2).Value = shForm.Range("b3").Value 'Reference #
.DataBodyRange(2, 3).Value = shForm.Range("c9").Value 'Reference #
.DataBodyRange(2, 4).Value = shForm.Range("d9").Value 'Reference #
.DataBodyRange(2, 5).Value = shForm.Range("g9").Value 'Reference #


End With


    
ThisWorkbook.Save 'Save this workbook

End Sub

Sub TransferToTable()

Dim iMessage As VbMsgBoxResult

iMessage = MsgBox("Do you want to transfer the data?", vbYesNo + vbQuestion, "Confirmation")

If iMessage = vbNo Then Exit Sub

    Call Transfer

End Sub

Upvotes: 1

Views: 12

Answers (0)

Related Questions