Help with inputting the value in the back sheet using VBA code

[maorlov]

Hi all,

I have a problem with my code and hope someone can help me. The code in the worksheet is the below. The idea is that the user inputs value in one sheet and that is automatically transferred to the back "Data sheet". The code below is correct in that sense. Problem is that cell in the Data Sheet is a formula (sum of 2 cells) and when the user, for example, inputs the value in one cell from the input sheet to the "Data Sheet", all the formulas in the "Data sheet" become values. My question is how to keep them as formulas? So ideally, the cells that are not changed should remain the same, as formulas, as they were before the change.
Thank you!

Martina

Private Sub Worksheet_Change(ByVal Target As Range)


Dim RngToProcess As Range
Set RngToProcess = Intersect(Range("D14:E16, D18:E20, D24:E26, D29:E31, D34:E36,C41:C43, F41:F43, D47:E49, D51:E53, D57:E59, D62:E64, D67:E69"), Target)
If Not RngToProcess Is Nothing Then
For Each cll In RngToProcess.Cells
If IsNumeric(cll.Value) And Range("Q5") = 1 Then Worksheets("Data Sheet").Range(cll.Offset(108).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 2 Then Worksheets("Data Sheet").Range(cll.Offset(108, 8).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 4 Then Worksheets("Data Sheet").Range(cll.Offset(108, 16).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 3 Then Worksheets("Data Sheet").Range(cll.Offset(108, 24).Address).Value = cll.Value
If IsNumeric(cll.Value) And Range("Q5") = 5 Then Worksheets("Data Sheet").Range(cll.Offset(108, 32).Address).Value = cll.Value
Next cll
End If


End Sub

[snb]

Please use code tags !

You'd better use a userform instead of a worksheet as inputform.

Private Sub Worksheet_Change(ByVal Target As Range)
  if not Intersect(Range("D14:E16, D18:E20, D24:E26, D29:E31,  D34:E36,C41:C43, F41:F43, D47:E49, D51:E53, D57:E59, D62:E64, D67:E69"),  Target) Is Nothing Then
    For Each cll In target
      If IsNumeric(cll) Then Worksheets("Data Sheet").Range(cll.Offset(108,choose(Range("Q5"),0,8,24,16,32)).Address) = cll.formula
    next
  End If
End Sub

[maorlov]

thank you!