Populating sheet 2 with combobox values after sheet 1 full

[sammu78]

Hi,

Below is the code for completing Sheet 1 using various comboxes on a User form. There are only 15 lines to which values can be entered on sheet 1. After the 15 lines have been completed, I would like to begin adding new data to sheet 2. However, I'm not really sure where to begin in terms of setting up a counter and then calling sheet 2 once the counter hits my limit. Sheet 2 is exactly identical to sheet 1 in layout and also has 15 lines.

Any feedback/tips would be appreciated. Thanks in advance. NOTE: I'm a newbie to VBA and have been putting things together from viewing responses to other questions.

Private Sub btnAdd_Click()
' This button will add medication info to excel worksheet

'RowCount will help find next empty row
Dim RowCount As Long
RowCount = Worksheets("Sheet1").Range("D10").CurrentRegion.Rows.Count

With Worksheets("Sheet1").Range("D10")
.Offset(RowCount - 3, 0).Value = Me.cboMedication.Value
.Offset(RowCount - 3, 3).Value = Me.txtDose.Text
.Offset(RowCount - 3, 4).Value = Me.cboRoute.Value
.Offset(RowCount - 3, 5).Value = cboFrequency.Value

End With

'This will clear the combo boxes
cboMedication.Value = ""
txtDose.Text = ""
cboRoute.Value = ""
cboFrequency.Value = ""

End Sub

[sammu78]

Just thinking about this. Would using a counter to keep track of how many rows entered help with determining when to start using the 2nd sheet? I get a 'subscript out of range" error with the line in blue text. Not sure how to fix this.

Such as below:
Private Sub btnAdd_Click()
' This button will add medication info to excel worksheet


'RowCount1 and RowCount2 will help find next empty row
Dim RowCountSheet1 As Long
Dim RowCountSheet2 As Long
'MedCounter to keep track of entries on sheet 1
Dim MedCounter As Integer

MedCounter = 0
RowCountSheet1 = Worksheets("Sheet1").Range("D10").CurrentRegion.Rows.Count
RowCountSheet2 = Worksheets("Sheet2").Range("D10").CurrentRegion.Rows.Count

If MedCounter < 15 Then
With Worksheets("Sheet1").Range("D10")
.Offset(RowCountSheet1 - 3, 0).Value = Me.cboMedication.Value
.Offset(RowCountSheet1 - 3, 3).Value = Me.txtDose.Text
.Offset(RowCountSheet1 - 3, 4).Value = Me.cboRoute.Value
.Offset(RowCountSheet1 - 3, 5).Value = cboFrequency.Value
MedCounter = MedCounter + 1
End With
Else
With Worksheets("Sheet2").Range("D10")
.Offset(RowCountSheet2 - 3, 0).Value = Me.cboMedication.Value
.Offset(RowCountSheet2 - 3, 3).Value = Me.txtDose.Text
.Offset(RowCountSheet2 - 3, 4).Value = Me.cboRoute.Value
.Offset(RowCountSheet2 - 3, 5).Value = cboFrequency.Value
End With
End If
'This will clear the combo boxes
cboMedication.Value = ""
txtDose.Text = ""
cboRoute.Value = ""
cboFrequency.Value = ""

End Sub