If texbox value begins with "E" then send to another sheet.

[aintlifegrand79]

I have set up a userform which takes data from about 50 textboxes and enters
it into 7 different worksheets. What I need is when a value in the
tbProjectNumber textbox begins with "E" (Upper or lower case) to send the
information to an 8th worksheet as well as the other 7 worksheets. Here is a
portion of my code and thank you in advance for your help.

Private Sub SaveButton_Click()
' Activate Sheet1
Sheet1.Activate
' Check to see if project number is already entered
If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then
ufAlreadyEntered.Show
If Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet1(Project Type)
Cells(NextRow, 1) = tbProjectNumber.Text
Cells(NextRow, 2) = tbAEName.Text
Cells(NextRow, 3) = tbSiteOwnerName.Text
Cells(NextRow, 4) = tbPGLead.Text
Cells(NextRow, 5) = cbProjectType.Text
Cells(NextRow, 6) = cbProjectCategory.Text
' Activate Sheet8
Sheet8.Activate
' Transfer to Sheet8(Experience List)
If tbProjectNumber.Value = "E******" Then
Cells(NextRow, 1) = tbProjectNumber.Value
Cells(NextRow, 2) = tbSiteOwnerName.Text
Cells(NextRow, 3) = tbSiteLocation.Text
Cells(NextRow, 4) = tbSiteName.Text
Cells(NextRow, 5) = tbSiteUnitNumber.Text
Cells(NextRow, 6) = cbApplication.Text
Cells(NextRow, 7) = tbQuantity.Text
Cells(NextRow, 8) = tbHPRating.Text
Cells(NextRow, 9) = tbOutputVoltage.Text
Cells(NextRow, 10) = tbDelivery.Text
Cells(NextRow, 11) = tbVFDType.Text
End If
' Set the controls for the next entry
tbProjectNumber.SetFocus
Sheet7.Activate
End If
End Sub

 

[Dave Peterson]

Untested, uncompiled. Watch for typos.

(And I didn't understand the first portion of your code.)

Option Explicit
Private Sub SaveButton_Click()

Dim mySheetNames As Variant
Dim myOtherSheetName As String
Dim sCtr As Long
Dim NextRow As Long

mySheetNames = Array("sheet1", "sheet99", "testsheet", _
"just", "a", "sheet", "namehere")

myOtherSheetName = "Experience List"

For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(sCtr))
NextRow = Application.WorksheetFunction.CountA(.Range("A:A")) + 1
.Cells(NextRow, 1).Value = Me.tbProjectNumber.Text
.Cells(NextRow, 2).Value = Me.tbAEName.Text
.Cells(NextRow, 3).Value = Me.tbSiteOwnerName.Text
.Cells(NextRow, 4).Value = Me.tbPGLead.Text
.Cells(NextRow, 5).Value = Me.cbProjectType.Text
.Cells(NextRow, 6).Value = Me.cbProjectCategory.Text
End With
Next sCtr

If LCase(Left(Me.tbProjectNumber.Value, 1)) = LCase("E") Then
With Worksheets(myOtherSheetName)
NextRow = Application.WorksheetFunction.CountA(.Range("A:A")) + 1
.Cells(NextRow, 1).Value = Me.tbProjectNumber.valuealue
.Cells(NextRow, 2).Value = Me.tbSiteOwnerName.Text
.Cells(NextRow, 3).Value = Me.tbSiteLocation.Text
.Cells(NextRow, 4).Value = Me.tbSiteName.Text
.Cells(NextRow, 5).Value = Me.tbSiteUnitNumber.Text
.Cells(NextRow, 6).Value = Me.cbApplication.Text
.Cells(NextRow, 7).Value = Me.tbQuantity.Text
.Cells(NextRow, 8).Value = Me.tbHPRating.Text
.Cells(NextRow, 9).Value = Me.tbOutputVoltage.Text
.Cells(NextRow, 10).Value = Me.tbDelivery.Text
.Cells(NextRow, 11).Value = Me.tbVFDType.Text
End If
End Sub