Need help about vba code for User form in sheet

[maiwarits]

Hi guys,

I tried to create user form for my report in excel sheet not a user form in VBA code.
I try many code on the internet but it not match my expect.

Please see the detail below, and suggest me for the code.

This user form in the sheet name "EnterIn" the value is actually in the column B but I use center across selection.
B3= 11-Jan-2023
B4= CYL-004
B5= Operation gas
B6= Pure Oxygen (O2) 99.9995% min
B7= 5
B8= 20000

Value in B3-B8 will place in destination sheet base on header.

This sheet is the destination sheet with "InHistory" name.

Explanation:
After entry the data in B3-B8 then click SUBMIT button the data should go in to the destination sheet then clear the value in B3-B8.
The value should go only the value not a formular in cell.

This my starter code:

Public Sub EnterIn()
    Dim wsEnterIN As Worksheet 'define source sheet
    Set wsEnterIN = ThisWorkbook.Worksheets("EnterIn")

    Dim wsInHistory As Worksheet 'define destination sheet
    Set wsInHistory = ThisWorkbook.Worksheets("InHistory")

    Dim DestRow As Long
    DestRow = 6 'start in row 2 in destination sheet







End Sub

Thanks,

 

[Legacy 143009]

I hope this works:

Sub Submit_Click()
  With Worksheets("InHistory")
  Worksheets("EnterIN").Range("B3:B8").Copy
  .Cells(.Cells(Rows.Count, 2).End(xlUp).Row + 1, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Worksheets("EnterIN").Range("B3:B8").Value = ""
  End With
End Sub

 

[maiwarits]

I hope this works:

Sub Submit_Click()
  With Worksheets("InHistory")
  Worksheets("EnterIN").Range("B3:B8").Copy
  .Cells(.Cells(Rows.Count, 2).End(xlUp).Row + 1, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Worksheets("EnterIN").Range("B3:B8").Value = ""
  End With
End Sub

The code clears my formula in the cell also. Is it possible to don't clear the formula in the cell?

 

[Legacy 143009]

If there is formula in cells, howcome do you expect that it won't display any results?

 

[maiwarits]

If there is formula in cells, howcome do you expect that it won't display any results?

Sorry for unclear explanation.

The value in gray box (B3, B4, B5, B8) have formular they will show after B6 entry any value.

So, it should clear the data in cell B6,B7 only after click submit button.
but B3:B7 it should copy only the value not a formular to destination sheet.

Btw, Many Thanks for your kind helpful!

 

[Legacy 143009]

Change this line:

Worksheets("EnterIN").Range("B3:B8").Value = ""

To this:

Worksheets("EnterIN").Range("B6:B7").Value = ""

 

[maiwarits]

Change this line:

Worksheets("EnterIN").Range("B3:B8").Value = ""

To this:

Worksheets("EnterIN").Range("B6:B7").Value = ""

Hi, Sorry for late reply

Many Thanks for your help!