CORRECTING CODE TRNASFER DATA FROM USERFORM TO SHEETS

0

hi, everyone i need help me about code transfer data from userform to sheet1,sheet2 

 i have mistakes when press command button (posting) in userform it begening from row29 to row 35 in my case it must be value 800 not 1000 in sheet1  moreover when transfer data to sheet2 some datas ignore  and the values are wrong 

Answer
Discuss

Answers

0
Selected Answer

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).

Discuss

Discussion

thanks for your answering  about sheet1 formula from E28 to E29 the formula it's exist but when i fill data in userform press  command posting  occure the mistake
e28to e29 become no formula 
as to sheet2 do me faver   give me the completed code to well work  i have  the wrong in this code  :
Sub transferData() Dim Ws As Worksheet Set Ws = Worksheets("ورقة1") Ws.Range(Ws.Cells(i, 1), Ws.Cells(i, 6)).Copy Dim Lastrow As Long Dim erow As Long Lastrow = Sheets("ورقة1").Range("a" & Rows.Count).End(xlUp).Row For i = 3 To Lastrow erow = Sheets("ورقة2").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row Range(Cells(i, 1), Cells(i, 6)).Copy Destination:=ورقة2.Cells(erow, 1) Next i ورقة2.Cells(erow, 6) = WorksheetFunction.Sum(Worksheets("ورقة2").Range("e3:e35")) End Sub

messege error"run time error 1004"
application-define or object defined error
the wrong in this line:   Ws.Range(Ws.Cells(i, 1), Ws.Cells(i, 6)).Copy
leopard (rep: 6) Jun 19, '19 at 4:26 am
The error is caused by the variable i which is 0 at the point of your code where the call to define the range is made.
Variatus (rep: 3533) Jun 19, '19 at 4:54 am
once again  thanks so much  despite this code works and transfer the data but still not perfect  transfer  all of datas except  the coordinates  like color  i hope  transfer the data  the same in sheet1 to sheet2  data and coordinates , colors every thing  moreover  the the row of total  doesn't transfer like the rest datas 
leopard (rep: 6) Jun 19, '19 at 5:57 am
I again draw your attention to the last line of code which writes a total drawn from range E3:E35. This range should be wrong. To get the total you want you may have to check this specification.
Variatus (rep: 3533) Jun 19, '19 at 11:24 am
now the code does work except one  thing is the total  here i would also row 33,34,35 these are linked with them  i don't mean collecting the value and text from e2 to e35 
i 'd like just  show rows 33,34,35(subtotal, shipping,total) 
by the way i changed the last line of code  from range e33:e35   it doesn't change  the result
notes: not sum from range(e33:e35)  every row separat of the other to more clear 
e33=e19 & e35=e34+e33
i hope this help you to understand what i exectly  what i would 
leopard (rep: 6) Jun 19, '19 at 12:02 pm
If E33 = E19 then E19 can be ignored. If E35 = E33+E34 then E35 is all you want because it includes everything. I have amended the code accordingly.
Variatus (rep: 3533) Jun 19, '19 at 10:32 pm
i appreciate your efforts and i'm really sorry for upsting  but, honestly the problem is still  continued   i'm newer in vba exel   i attaced the file after enter your cod  you find file  above in the first share 
leopard (rep: 6) Jun 20, '19 at 4:24 am
On your Sheet1, copy E28 to E29.
In the code, change Target.Offset(LastRow, 5).Value = Sheet2.Range("E35").Value to Target.Offset(LastRow, 5).Value = Sheet1.Range("E35").Value. E35 must be taken from Sheet1, not Sheet2.
Delete all the code in the ThisWorkbook module. It is ineffective (not enabled) but if steps would be taken to make it run it would create havoc in your workbook.
Variatus (rep: 3533) Jun 20, '19 at 5:32 am
finally  i found the problem is  some rows are empty thats why ignored  them 
thank so much for your efforts and especially  special explaining 
leopard (rep: 6) Jun 20, '19 at 3:33 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login