Finding and copying from 1 worksheet to another.
 

I have a userform uses a button to look up an existing values (textbox:
tbExistingProjectNumber)in multiple (9)worksheets and then changes it to a
new value (textbox: tbChangeProjectNumberTo) on those worksheets. My problem
is that if the tbEXistingProjectNumber begins with a "P" and
tbChangeProjectNumberTo begins with an "E" I need to have certain information
from worksheet 7 copied to the next empty row of worksheet 8. I also need to
make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub