Need Userform to Write to Multiple Sheets with Different Offset Criteria

[eyeguy]

Hi all,

I've been searching around for some help on this for a while but haven't come up with anything yet that is working. I currently have a userform set to enter data on potential business partners for our sales team into the next available row on one worksheet. However, I am looking to further automate the workbook by entering this data not only into the primary table, but also into a separate sheet in the same workbook that is using dynamic ranges to add to, sort, and repopulate a combobox on a separate userform showing appointments with existing business partners (as the new partners would be after being entered originally).

That's a rather long-winded explanation of the intent, and the functionality of all but one of these pieces is already worked out. The wall I'm hitting (and I'm sure it's one of my own making) is that I can write to one worksheet in a next available row format--as intended, since each entry must be placed on its own unique row regardless of which userform it's coming from--but I cannot get the form to write to a separate worksheet selecting the next available cell in a column. The reason the second worksheet must be written by column is that we have different numbers of partners in separate regions, and each region has (by necessity of size) its own column.

I had intended to enter data uniformly in the first worksheet on the next available row regardless of region. The entry on the second worksheet is to be determined by the value of AreaOptionButton1 (True goes to the next available cell in column A), AreaOptionButton2 (True goes to the next available cell in column B), and AreaOptionButton3 (True goes to the next available cell in column C).

The functioning code I currently have for the first worksheet is as follows:

Private Sub SaveRecordCommandButton_Click()

Dim lngWriteRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

lngWriteRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

If lngWriteRow < 2 Then lngWriteRow = 2

    ws.Range("A" & lngWriteRow) = ContactDate
    ws.Range("B" & lngWriteRow) = AssociateComboBox.Value
    ws.Range("R" & lngWriteRow) = CompanyNameTextBox.Value
    ws.Range("S" & lngWriteRow) = PartnerNameTextBox.Value
    ws.Range("J" & lngWriteRow) = NotesTextBox.Value
    ws.Range("F" & lngWriteRow) = EmailAddressTextBox.Value
    ws.Range("G" & lngWriteRow) = PhoneNumberTextBox.Value
    ws.Range("T" & lngWriteRow) = "New Partner"

ContactDate.Visible = True
ContactDate = Int(Date)
AssociateComboBox.Clear
    With AssociateComboBox
        .AddItem "SP1"
        .AddItem "SP2"
        .AddItem "SP3"
        .AddItem "SP4"
        .AddItem "SP5"
    End With
CompanyNameTextBox.Value = ""
PartnerNameTextBox.Value = ""
PhoneNumberTextBox.Value = ""
EmailAddressTextBox.Value = ""
NotesTextBox.Value = ""
AssociateComboBox.SetFocus

ActiveWorkbook.Save

End Sub

Any help would be greatly appreciated (and your patience in reading through this rambling request is already very appreciated as well). I'm just annoyed to have come so far in this and then to hit a wall on something that should be so simple.

[karedog]

Hi,

Put this new sub code in your userform :

Sub WriteToAnotherSheet(param)
  Dim lngWriteRow As Long, ws As Worksheet, selColumn As Long
  Set ws = Worksheets("Sheet2")

  If AreaOptionButton1.Value = True Then
     selColumn = 1
  ElseIf AreaOptionButton2.Value = True Then
     selColumn = 2
  ElseIf AreaOptionButton3.Value = True Then
     selColumn = 3
  Else
     Exit Sub
  End If

  lngWriteRow = ws.Cells(Rows.Count, selColumn).End(xlUp).Offset(1, 0).Row
  If lngWriteRow < 2 Then lngWriteRow = 2
  ws.Cells(lngWriteRow, selColumn) = param
End Sub

Then you can call this sub whenever you want to put a new value to other worksheet, for example :

...
...
EmailAddressTextBox.Value = ""
NotesTextBox.Value = ""
AssociateComboBox.SetFocus

WriteToAnotherSheet "Hello World"

ActiveWorkbook.Save

End Sub

[eyeguy]

Perfect, thanks!