Thread: Macro to transfer data from 1 tab to another

Help!

I need to create a macro so that I input the answers from the survey, which then exports the answers onto a second tab for the order. Once that order is transferred to the second tab, I need the survey form to wipe clear for new data so transfer to the next available row on the second tab. I started but have hit a brick wall.

Please help!

Refer to your file attached.

Is this anything like what you're trying to do?

Dion

Private Sub CommandButton1_Click()

Dim wsSrc As Worksheet
Dim wsTrg As Worksheet
Dim sumA As Integer
Dim sumB As Integer

Set wsSrc = Sheets("Quiz")
Set wsTrg = Sheets("Data")
sumA = 0
sumB = 0

'Make sure all questions have been answered
If Worksheets(1).Question1A.Value = False And Worksheets(1).Question1B.Value = False Then
    MsgBox "Please answer question 1.", vbCritical
    Exit Sub
End If
If Worksheets(1).Question2A.Value = False And Worksheets(1).Question2B.Value = False Then
    MsgBox "Please answer question 2.", vbCritical
    Exit Sub
End If
If Worksheets(1).Question3A.Value = False And Worksheets(1).Question3B.Value = False Then
    MsgBox "Please answer question 3.", vbCritical
    Exit Sub
End If
If Worksheets(1).Question4A.Value = False And Worksheets(1).Question4B.Value = False Then
    MsgBox "Please answer question 4.", vbCritical
    Exit Sub
End If
If Worksheets(1).Question5A.Value = False And Worksheets(1).Question5B.Value = False Then
    MsgBox "Please answer question 5.", vbCritical
    Exit Sub
End If

'Save values to Data sheet
If Worksheets(1).Question1A.Value = True Then
    wsTrg.Range("B50000").End(xlUp).Offset(1, 0).Value = "Y"
    sumA = sumA + 1
Else
    wsTrg.Range("B50000").End(xlUp).Offset(1, 0).Value = "N"
    sumB = sumB + 1
End If

If Worksheets(1).Question2A.Value = True Then
    wsTrg.Range("C50000").End(xlUp).Offset(1, 0).Value = "Y"
    sumA = sumA + 1
Else
    wsTrg.Range("C50000").End(xlUp).Offset(1, 0).Value = "N"
    sumB = sumB + 1
End If

If Worksheets(1).Question3A.Value = True Then
    wsTrg.Range("D50000").End(xlUp).Offset(1, 0).Value = "Y"
    sumA = sumA + 1
Else
    wsTrg.Range("D50000").End(xlUp).Offset(1, 0).Value = "N"
    sumB = sumB + 1
End If

If Worksheets(1).Question4A.Value = True Then
    wsTrg.Range("E50000").End(xlUp).Offset(1, 0).Value = "Y"
    sumA = sumA + 1
Else
    wsTrg.Range("E50000").End(xlUp).Offset(1, 0).Value = "N"
    sumB = sumB + 1
End If

If Worksheets(1).Question5A.Value = True Then
    wsTrg.Range("F50000").End(xlUp).Offset(1, 0).Value = "Y"
    sumA = sumA + 1
Else
    wsTrg.Range("F50000").End(xlUp).Offset(1, 0).Value = "N"
    sumB = sumB + 1
End If

Worksheets(1).Question1A.Value = False
Worksheets(1).Question1B.Value = False
Worksheets(1).Question2A.Value = False
Worksheets(1).Question2B.Value = False
Worksheets(1).Question3A.Value = False
Worksheets(1).Question3B.Value = False
Worksheets(1).Question4A.Value = False
Worksheets(1).Question4B.Value = False
Worksheets(1).Question5A.Value = False
Worksheets(1).Question5B.Value = False

End Sub