I am trying to build a form to submit data to a second worksheet (basically a database) but would like to be able to recall existing entries to update. I cannot figure out how to overwrite an entry with a given field.
So sheet1 has 12 cells and sheet2 will have 20-30 entries, each row having 12 columns. The first cell is a person's name, and the rest are subject to change, and I would like to be able to search for the entry by name and overwrite the existing data. Below is the macro I am using to copy and paste. Any thoughts?
Sub Test()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Set sh1 = Worksheets("LT Input")
Set sh2 = Worksheets("LT Data")
Set r1 = sh1.Range("E2:E12")
Set r2 = sh2.Cells(Rows.Count, 1).End(xlUp)(2)
r1.Copy
r2.PasteSpecial Transpose:=True
Application.CutCopyMode = False
r1.ClearContents
End Sub
Thanks in advance.
So sheet1 has 12 cells and sheet2 will have 20-30 entries, each row having 12 columns. The first cell is a person's name, and the rest are subject to change, and I would like to be able to search for the entry by name and overwrite the existing data. Below is the macro I am using to copy and paste. Any thoughts?
Sub Test()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Set sh1 = Worksheets("LT Input")
Set sh2 = Worksheets("LT Data")
Set r1 = sh1.Range("E2:E12")
Set r2 = sh2.Cells(Rows.Count, 1).End(xlUp)(2)
r1.Copy
r2.PasteSpecial Transpose:=True
Application.CutCopyMode = False
r1.ClearContents
End Sub
Thanks in advance.
