Userform enters data into a separate worksheet. How do I make it then extrapolate that data to another worksheet, on two new rows?

Question

I've created a UserForm which opens when I select a command button. The purpose of it is to capture data from a selected row concerning a "tour" - i.e. the tour code, start date and end date, and then for me to "split" the tour, for which I need to enter new tour codes, start dates and end dates.

The data from the form goes into a separate sheet (called "splits"), so that I have a record of the original tour details, and the new tour details in one sheet. The data is laid out as follows:

So the userform inserts all the data into these columns - this works fine, and I'm happy with it.

I'm trying to then make it then copy the details of the new tours onto the bottom two rows of the original worksheet ("Final tours"), and delete the old tour details from this sheet. But I don't know how to make it copy the new tour details (so D:F and G:H on the "Splits" sheet), and then delete the original row?

This is my code at the moment:

Object Explicit

    Dim originalRow As Range

Private Sub UserForm_Initialize()
    Set originalRow = ActiveCell.EntireRow
    With Me
        .OriginalTourCode.Value = originalRow.Cells(1).Value
        .OriginalStartDate.Value = originalRow.Cells(2).Value
        .OriginalEndDate.Value = originalRow.Cells(3).Value
    End With
End Sub

Private Sub SplitTourCommand_Click()

    Dim ctrl As Control
    Dim wsSplits As Worksheet
    Set wsSplits = Sheets("Splits")
    Dim wsTours As Worksheet
    Set wsTours = Sheets("Final Tours")
    Dim WSheet As Variant
    Dim DTable As Variant, RowCount As Long

 Application.ActiveWorkbook.Save

    For Each WSheet In ActiveWorkbook.Worksheets
        If wsTours.AutoFilterMode Then
            If wsTours.FilterMode Then
                wsTours.ShowAllData
            End If
        End If
        For Each DTable In wsTours.ListObjects
            If DTable.ShowAutoFilter Then
                DTable.Range.AutoFilter
                DTable.Range.AutoFilter
            End If
        Next DTable
    Next WSheet

    With wsSplits.Cells(Rows.Count, "A").End(xlUp).Row
        With .Offset(2, 0).EntireRow.Row
            .Cells(1) = OriginalTourCode.Text
            .Cells(2) = OriginalStartDate.Text
            .Cells(3) = OriginalEndDate.Text
            .Cells(4) = NewTourCode1.Text
   .Cells(5) = NewStartDate1.Text
    .Cells(6) = NewEndDate1.Text
    .Cells(7) = NewTourCode2.Text
    .Cells(8) = NewStartDate2.Text
    .Cells(9) = NewEndDate2.Text
    .Cells(10) = ReasonForSplit.Text
    .Cells(11).Value = Date

        End With
    End With

    'no need to copy from the Splits sheet since you have the data
    ' in the form fields
    With wsTours.Cells(Rows.Count, "A").End(xlUp)
        .Offset(1, 0).Value = NewTourCode1.Text
        .Offset(1, 1).Value = NewStartDate1.Text
        .Offset(1, 2).Value = NewEndDate1.Text
        .Offset(2, 0).Value = NewTourCode2.Text
        .Offset(2, 1).Value = NewStartDate2.Text
        .Offset(2, 2).Value = NewEndDate2.Text
    End With

    originalRow.Delete 'remove the row the the split tour

    MsgBox "Tour " & wsSplits.Cells(ActiveCell.Row, "A").Value & _
      " has been split to " & wsSplits.Cells(ActiveCell.Row, "D").Value & _
      " and " & wsSplits.Cells(ActiveCell.Row, "G")

End Sub


Private Sub CloseCommand_Click()

Unload Me

End Sub

Any thoughts? All help much appreciated.

What exactly is your stumbling block? Looks like you know how to copy between sheets. As an aside, I suggest you read this stackoverflow.com/questions/10714251/… — SJR

Tim Williams Tim Williams · Accepted Answer · 2019-01-17T00:05:07

You can do something like this:

Option Explicit

Dim originalRow As Range

Private Sub UserForm_Initialize()
    Set originalRow = ActiveCell.EntireRow '<< store this row in a global variable
    With Me
        .OriginalTourCode.Value = originalRow.Cells(1).Value
        .OriginalStartDate.Value = originalRow.Cells(2).Value
        .OriginalEndDate.Value = originalRow.Cells(3).Value
    End With
End Sub

Private Sub SplitTourCommand_Click()

    Dim ctrl As Control
    Dim wsSplits As Worksheet
    Set wsSplits = Sheets("Splits")
    Dim wsTours As Worksheet
    Set wsTours = Sheets("Final Tours")
    Dim WSheet As Variant
    Dim DTable As Variant, RowCount As Long

    'snipped autofilter code...

    With wsSplits.Cells(Rows.Count, "A").End(xlUp)
        .Offset(1, 0) = workOrderDescription
        With .Offset(2, 0).EntireRow
            .Cells(1) = OriginalTourCode.Text
            .Cells(2) = OriginalStartDate.Text
            .Cells(3) = OriginalEndDate.Text
            'etc etc for the other cells
        End With
    End With

    'no need to copy from the Splits sheet since you have the data
    ' in the form fields
    With wsTours.Cells(Rows.Count, "A").End(xlUp)
        .Offset(1, 0).Value = NewTourCode1.Text
        .Offset(1, 1).Value = NewStartDate1.Text
        .Offset(1, 2).Value = NewEndDate1.Text
        .Offset(2, 0).Value = NewTourCode2.Text
        .Offset(2, 1).Value = NewStartDate2.Text
        .Offset(2, 2).Value = NewEndDate2.Text
    End With

    originalRow.Delete 'remove the row the the split tour

    MsgBox "Tour " & ws.Cells(ActiveCell.Row, "A").Value & _
      " has been split to " & ws.Cells(ActiveCell.Row, "D").Value & _
      " and " & ws.Cells(ActiveCell.Row, "G")

End Sub

Userform enters data into a separate worksheet. How do I make it then extrapolate that data to another worksheet, on two new rows?

1 Answers