Import from Excel to VBA and export to new sheet


New Member
Dec 29, 2016
Hi folks,

Really hoping that someone can tell me if this is possible. I'm working on a little project within my job and its my first time using VBA.

I've built a form that will allow our analysts to review data using dropdown menus. So for example, an end customer will leave a review for the agent who handled their case. This data is automatically exported to a spreadsheet. The first 4 columns of each row contain the data received from the customer:
Case number
Agent Name
Week Number

The 8 columns which follow in each row allow the analyst to examine this review Each column contains a dropdown menu which I have created using a ComboBox in VBA. Finally at the very end of the row, you have a submit button.

So here is what I need to do:

I need to automatically pull the data from the first 4 columns into the corresponding columns in the VBA form.
I need to submit all of the completed data including the original data using the submit button to a new sheet.
As each row is submitted, I need it to disappear from the list so that the next row moves up automatically on the VBA form.

I have the basic form created but this is where I'm hitting a brick wall on these issues.

If anyone could help, it would be awesome.


Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Assuming that
your data is on Sheet1
the Case numbers are unique
form name is UserForm1
buttons are named as shown
combo box names are Combox1 thru 8
try this:

Put this code in the userform code page.

Private Sub UserForm_Activate()
Dim i As Integer
[COLOR=#00FF00]'bring the text from the active row into the text boxes[/COLOR]
txt_Case.Text = Cells(ActiveCell.Row, 1)
txt_Agent.Text = Cells(ActiveCell.Row, 2)
txt_Week.Text = Cells(ActiveCell.Row, 3)
txt_Rating.Text = Cells(ActiveCell.Row, 4)
[COLOR=#00FF00]'populate the combo boxes[/COLOR]
For i = 1 To 8
    UserForm1.Controls("ComboBox" & i).List = Sheets(2).Range("A1:A25").Value
End Sub

Private Sub btn_Submit_Click()
Dim sht As Worksheet
Dim shtName As String
Dim Rng As Range
[COLOR=#00FF00]'set the sheet name to the Case text[/COLOR]
shtName = txt_Case.Text
On Error Resume Next
[COLOR=#00FF00]'search for the sheet name[/COLOR]
Set sht = Sheets(shtName)
On Error GoTo 0
[COLOR=#00FF00]'if it doesn't exist[/COLOR]
If sht Is Nothing Then
[COLOR=#00FF00]    'create the sheet[/COLOR]
    Worksheets.Add.Name = shtName
[COLOR=#00FF00]    'set the name[/COLOR]
    Set sht = Sheets(shtName)
End If
[COLOR=#00FF00]'go back to Sheet1[/COLOR]
With sht
[COLOR=#00FF00]'find the first free row in the new sheet[/COLOR]
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
[COLOR=#00FF00]    'copy the header row[/COLOR]
    Worksheets("Sheet1").Range("1:1").Copy _
[COLOR=#00FF00]    'copy the text from the userform[/COLOR]
    .Cells(LastRow, 1) = UserForm1.txt_Case
    .Cells(LastRow, 2) = UserForm1.txt_Agent
    .Cells(LastRow, 3) = UserForm1.txt_Week
    .Cells(LastRow, 4) = UserForm1.txt_Rating
    .Cells(LastRow, 5) = UserForm1.ComboBox1
    .Cells(LastRow, 6) = UserForm1.ComboBox2
    .Cells(LastRow, 7) = UserForm1.ComboBox3
    .Cells(LastRow, 8) = UserForm1.ComboBox4
    .Cells(LastRow, 9) = UserForm1.ComboBox5
    .Cells(LastRow, 10) = UserForm1.ComboBox6
    .Cells(LastRow, 11) = UserForm1.ComboBox7
    .Cells(LastRow, 12) = UserForm1.ComboBox8
End With
[COLOR=#00FF00]'find the case name on sheet1[/COLOR]
With Sheets("Sheet1").Range("A:A")
    Set Rng = .Find(What:=shtName, _
        After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
    If Not Rng Is Nothing Then
[COLOR=#00FF00]        'if you find it, delete the row[/COLOR]
    End If
End With

Unload UserForm1
End Sub

Private Sub btn_Cancel_Click()
Unload UserForm1

End Sub

Create one button on the data sheet that will show the userform with the following code:

When you click anywhere in a row, it will grab the text from that row and put it into the userform. At the end it will search for the Case number in the data sheet and delete the row.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back