CORRECTING CODE TRNASFER DATA FROM USERFORM TO SHEETS

Your Sheet1 has no formula in E29. Copy the formula from E28 to E29 and the remaining value comes out correct all the way down.

The code appears to work as it should. There is a problem with copying the formula in column E, however. You might circumvent that by not copying the formula. The code below will copy values instead.

'        Range(Cells(i, 1), Cells(i, 6)).Copy Destination:=Sheet2.Cells(erow, 1)
        ' replace the above with the following
        Range(Cells(i, 1), Cells(i, 6)).Copy
        Sheet2.Cells(erow, 1).PasteSpecial xlPasteValues

If you need a formula in Sheet2 you should construct it in VBA and assign it to the cell's Formula property. This can be done after pasting the value.

Your code lacks transparency in the way you address the worksheets. Most of the time you omit the object, like in Range(Cells(i, 1), Cells(i, 6)).Copy. The worksheet object is omitted. Therefore Excel will presume you mean the ActiveSheet. The ActiveSheet is Sheet1 because that is the sheet which has the button which called the form. The code doesn't change the ActiveSheet. Therefore Sheets("Sheet1").Range("e29") = TextBox6.Value is the same as Range("e29") = TextBox6.Value. You should decide on one way of addressing Sheet1. Best practise is to assign the sheet to a variable and refer to the variable.

Dim Ws As Worksheet
Set Ws = Worksheets("Sheet1")
Ws.Range(Ws.Cells(i, 1), Ws.Cells(i, 6)).Copy

Actually, VBA for Excel has a built-in variable of this type. It's called the CodeName. The CodeName of Worksheets("Sheet1") is Sheet1. If you change the tab name from "Sheet1" to "Invoice" that sheet could be addressed as Worksheets("Invoice") or still as Sheet1. The CodeName doesn't change with the tab name. This is useful if you allow the user to change the tab name (in fact, it's hard to forbid).

In your code you refer to Sheets("Sheet2"), Worksheets("Sheet2") and Sheet2 - three different syntaxes, all indicating the same sheet. If you wanted to confuse the reader of your code (normally that would be yourself), that's the way to do it.

Note that a workbook can have "Sheets" which aren't "Worksheets", for example pivot tables. The Sheets collection, therefore, is bigger than the collection of Worksheets. It's like all men are people but not all people are men. I recommend to call a man a man, and a worksheet a worksheet.

Edit 19 Jun 2019 16:55 =======================================

The code below has correct syntax and does work. Note that I changed the CodeName of both Sheet1 and Sheet2 to English and you may have to change it back to Arabic.

Sub TransferData()
    ' 20 Jun 2019

    Dim Target As Range
    Dim LastRow As Long
    Dim R As Long

    LastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    Set Target = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp)
    For R = 2 To LastRow
        Sheet1.Range(Sheet1.Cells(R, 1), Sheet1.Cells(R, 6)).Copy _
                Destination:=Target.Offset(R - 1)
        With Target.Offset(R - 1, 4)
            If .HasFormula Then .Value = Sheet1.Cells(R, 5).Value
        End With
    Next R

    Target.Offset(LastRow, 5).Value = Sheet2.Range("E35").Value
End Sub

Despite the correct syntax the code doesn't produce a sensible result, however. That is because of the parameters you have hard-programmed.

For R = 3 To LastRow copies from row 3 to row 20 of Sheet1 to row 6 and down in Sheet2. Since there are no data in Sheet1 above row 19, why start copying from row 3?

Sum(Sheet2.Range("E3:E35")) sums up the range E3:E35 of Sheet2. This range includes a lot of text and a date. It isn't clear why you might want to insert a sum of these values in the Notes column of the last row of the copied data.

Edit 19 Jun 2019 23:55 =======================================

I modified the above code to copy formats as well as values and then replace any formula in column E with its value.

Edit 20 Jun 2019 10:25 =======================================

I modified the above code to adjust the target row and the source of the total in the last line of code.

Set Target = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp) sets the target in column A of the last used row. The intention is write bewlow this cell, not in it. Therefore Destination:=Target.Offset(R - 1). The first R = 2. Therefore R-1 specifies the next empty row. If the first R would be 3 then the Offset should be R-2. Always one smaller than the first R. If you change the first R the offset must be redefined. The Next loop increments this value automatically. This definition is repeated in With Target.Offset(R - 1, 4).