Excel VBA for copying data from one sheet to another_What can be used for copying selected value of a drop down list?

[Sara2015]

Hi!

I am having trouble finding some solution for my problem.
In one sheet I have row based table with data to either be entered with free text (empty cells), or drop down lists where an option needs to be selected.

When all the rows are filled, I would like to copy the filled data to the second sheet in a column based table and even the selection from the drop down list to follow into the second sheet.

So far, all the data which is entered manually in the first sheet is being copied to the second except the "text" from the selected option of the drop down list.

My drop down list consists of the options:
[TABLE="width: 128"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1-0 Transportation[/TD]
[/TR]
[TR]
[TD]2-0 Customer[/TD]
[/TR]
[TR]
[TD]3-0 Supplier[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
That is how my script looks like and I am pretty sure that it is the bold marked string that is incorrect - any suggestions of how I would fix that:

Private Sub CommandButton1_Click()
Dim CustomerName As String, Responsible As String, Errortype As String
Worksheets("Sheet1").Select
CustomerName = Range("B2")
Responsible = Range("B3")
Errortype = Range("B4")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("A8").Select
If Worksheets("Sheet2").Range("A8").Offset(1, 0) <> "" Then
Worksheets("Sheet").Range("A8").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Responsible
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("B3:B4").ClearContents
End Sub

So my biggest concern is: How can I get the selected drop down list value to be copied to the second sheet.
Very thankful for any help here.

 

[dmt32]

Hi welcome to the board:

see if these changes to your code help:

Private Sub CommandButton1_Click()
    Dim CustomerName As String, Responsible As String, Errortype As String
    
    With Worksheets("Sheet1")
        CustomerName = .Range("B2")
        Responsible = .Range("B3")
        Errortype = .Range("B4")
    End With
    
    With Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        If LastRow < 9 Then LastRow = 9
        .Cells(LastRow, 1).Value = CustomerName
        .Cells(LastRow, 2).Value = Responsible
        .Cells(LastRow, 3).Value = Errortype
    End With


    Worksheets("Sheet1").Range("B3:B4").ClearContents
End Sub

I have added the line shown in RED but was not included in code posted - change code as required.

Hope Helpful

Dave

 

[Sara2015]

That works perfectly,
Many thanks Dave!!

 

[dmt32]

That works perfectly,
Many thanks Dave!!

welcome - many thanks for feedback

Dave