Go back to update my data with my data entry sheet

[AnnaC]

Hi, I created a data entry sheet (sheet1) where different people will fill it
out and the data will be saved in sheet2. I would like to know if is posible
to update old records by using the entry sheet (sheet1). I using Excel 2007,
but would like to have something to work in early versions as well.

Thank you.
AnnaC

 

[joel]

The answer is yes. You could have a person enter a name or ID into a cell
(box, userform,...) and then check if the name (or ID) is already located in
sheet 2. If it is you can copy the data in sheet 2 back into sheet 1 before
letting the person change sheet 1. Then when done put the data back into
sheet 2. If the person is not already in sheet 2 then clear sheet one and
have the person add all the information from scratch.

 

[AnnaC]

Thank you, I am not good at crating macros could I get a macro sample. Thank
you again.

 

[joel]

Sub GetData()
Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'clear Entry sheet
EntrySht.Cells.ClearContents
'Put New ID in New Row
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Range("A" & DataRow) = ID
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row
'example of moving data from data sheet
'to entry sheet
EntrySht.Range("A2") = .Range("B" & c.Row)
'move all old data from data sheet to
'entry sheet here
End With
End If

'Now let user edit the Entry sheet
'then when user is finished
'have them press a submit button
'and run another macro to move data
'from entry sheet to data sheet

'
End Sub
Sub submit()

Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("B" & DataRow) = EntrySht.Range("D4")
End Sub

 

[AnnaC]

Thank you for your help!

Sub GetData()
Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'clear Entry sheet
EntrySht.Cells.ClearContents
'Put New ID in New Row
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Range("A" & DataRow) = ID
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row
'example of moving data from data sheet
'to entry sheet
EntrySht.Range("A2") = .Range("B" & c.Row)
'move all old data from data sheet to
'entry sheet here
End With
End If

'Now let user edit the Entry sheet
'then when user is finished
'have them press a submit button
'and run another macro to move data
'from entry sheet to data sheet

'
End Sub
Sub submit()

Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("B" & DataRow) = EntrySht.Range("D4")
End Sub