There isn't a good shortcut way of moving the data without making the code
hard to understand. I think it is better just to move each piece of data one
at a time. You are moving C7 twice. Is this correct?
Sub GetData()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")
ID = EntrySht.Range("Q2")
'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
'clear Entry Sheet
EntrySht.Range("Q2").ClearContents
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row
'to entry sheet
EntrySht.Range("E5") = .Range("A" & DataRow)
EntrySht.Range("I5") = .Range("B" & DataRow)
EntrySht.Range("M5") = .Range("C" & DataRow)
EntrySht.Range("C7") = .Range("D" & DataRow)
EntrySht.Range("I7") = .Range("E" & DataRow)
EntrySht.Range("M7") = .Range("F" & DataRow)
EntrySht.Range("C7") = .Range("G" & DataRow)
EntrySht.Range("C9") = .Range("H" & DataRow)
EntrySht.Range("F9") = .Range("I" & DataRow)
EntrySht.Range("I9") = .Range("J" & DataRow)
EntrySht.Range("M9") = .Range("K" & DataRow)
EntrySht.Range("E11") = .Range("L" & DataRow)
EntrySht.Range("F19") = .Range("M" & DataRow)
EntrySht.Range("D21") = .Range("N" & DataRow)
EntrySht.Range("D26") = .Range("O" & DataRow)
EntrySht.Range("D33") = .Range("P" & DataRow)
EntrySht.Range("D36") = .Range("Q" & DataRow)
End With
End If
End Sub
Sub Submit()
Set EntrySht = Sheets("Input")
Set DataSht = Sheets("Data")
ID = EntrySht.Range("Q2")
'See if ID already exists
Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow
DataRow = NewRow
Else
DataRow = c.Row
End If
With DataSht
.Range("A" & DataRow) = EntrySht.Range("E5")
.Range("B" & DataRow) = EntrySht.Range("I5")
.Range("C" & DataRow) = EntrySht.Range("M5")
.Range("D" & DataRow) = EntrySht.Range("C7")
.Range("E" & DataRow) = EntrySht.Range("I7")
.Range("F" & DataRow) = EntrySht.Range("M7")
.Range("G" & DataRow) = EntrySht.Range("C7")
.Range("H" & DataRow) = EntrySht.Range("C9")
.Range("I" & DataRow) = EntrySht.Range("F9")
.Range("J" & DataRow) = EntrySht.Range("I9")
.Range("K" & DataRow) = EntrySht.Range("M9")
.Range("L" & DataRow) = EntrySht.Range("E11")
.Range("M" & DataRow) = EntrySht.Range("F19")
.Range("N" & DataRow) = EntrySht.Range("D21")
.Range("O" & DataRow) = EntrySht.Range("D26")
.Range("P" & DataRow) = EntrySht.Range("D33")
.Range("Q" & DataRow) = EntrySht.Range("D36")
End With
End Sub
"AnnaC" wrote:
> This is a code that Joel so kindly wrote for me, I add some range and delete
> some, but now I can not make it work right. I want to update the previous
> data using the data entry sheet, but I can’t make it write all the data from
> the (the cells next to the ID) row to the data entry sheet (Entry) or send
> the data back to the data sheet (data). Would somebody mind helping me with
> this?
>
> Sub GetData()
> Set EntrySht = Sheets("Input")
> Set DataSht = Sheets("Data")
>
> ID = EntrySht.Range("Q2")
>
> 'See if ID already exists
> Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues,
> lookat:=xlWhole)
> If c Is Nothing Then
> 'clear Entry Sheet
> EntrySht.Range("Q2").ClearContents
> Else
> 'move old data from data sheet to entry sheet
> With DataSht
> DataRow = c.Row
>
> 'to entry sheet
> EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E11,F19,D21,D26,D33,C36")
> = .Range("C" & c.Row)
> End With
> End If
>
>
> End Sub
> Sub Submit()
> Set EntrySht = Sheets("Input")
> Set DataSht = Sheets("Data")
>
> ID = EntrySht.Range("Q2")
>
> 'See if ID already exists
> Set c = DataSht.Columns("B").Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
> If c Is Nothing Then
> LastRow = DataSht.Range("B" & Rows.Count).End(xlUp).Row
> NewRow = LastRow
> DataRow = NewRow
> Else
> DataRow = c.Row
> End If
>
> 'Enter your code here to move data from entry sheet
> 'to data sheet
> DataSht.Range("C" & DataRow) =
> EntrySht.Range("E5,I5,M5,C7,I7,M7,C7,C9,F9,I9,M9,E11,F19,D21,D26,D33,C36")
>
> End Sub
>
>
> Thank you.
>
>
Similar Threads
