Import from Excel to VBA and export to new sheet

[bourkedarragh]

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
Rating

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.

Thanks

 

[portews]

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
Next
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]
Sheets("Sheet1").Activate
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 _
    Destination:=sht.Range("A1")
[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, _
        MatchCase:=False)
    If Not Rng Is Nothing Then
[COLOR=#00FF00]        'if you find it, delete the row[/COLOR]
        Rng.EntireRow.Delete
    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:

UserForm1.Show

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.