Here is the problem I am running into.
I sorting data into different tabs based on certain criteria but some data might need to go into 2 tabs.
For instance, Voicemail Migration & Telecommuters.
If the data = 1000, 1888, 1999 it needs to be placed in the Voicemail Migration Tab.
If the data = */* (any number on either side of /) then needs to be placed in the Telecommuters tab.
But if the data = 1000/*, 1888/* or 1999/* than the data should be place into both Voicemail Migration and Telecommuters.
The problem I am having is if I place a IF */* Then copy for Telecommuters and for Voicemail it will only copy the data to one of the tabs and not both.
This is the last piece of my script that I need to iron out before I can put into production.
Here is a copy of my code for your review:
Thanks for your help with this.
Jim
I sorting data into different tabs based on certain criteria but some data might need to go into 2 tabs.
For instance, Voicemail Migration & Telecommuters.
If the data = 1000, 1888, 1999 it needs to be placed in the Voicemail Migration Tab.
If the data = */* (any number on either side of /) then needs to be placed in the Telecommuters tab.
But if the data = 1000/*, 1888/* or 1999/* than the data should be place into both Voicemail Migration and Telecommuters.
The problem I am having is if I place a IF */* Then copy for Telecommuters and for Voicemail it will only copy the data to one of the tabs and not both.
This is the last piece of my script that I need to iron out before I can put into production.
Here is a copy of my code for your review:
Rich (BB code):
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim Ctr, LR As Long
Dim UM7911row
Dim UM7941row
Dim NOUM_7941row
Dim NOUM_7911row
Dim Voicemail_Migrationrow
Dim Telecommutersrow
Dim Confrm_WProw
Dim Polycomrow
Dim FAXrow
Dim OpenArearow
On Error GoTo Err_Execute
'Define starting past rows
UM7911row = 2
UM7941row = 2
NOUM_7941row = 2
NOUM_7911row = 2
Voicemail_Migrationrow = 2
Telecommutersrow = 2
Confrm_WProw = 2
Polycomrow = 2
FAXrow = 2
OpenArearow = 2
With Worksheets("BLDGMaster")
LR = .Cells(Rows.Count, 15).End(xlUp).Row
For Ctr = 3 To LR Step 1
If .Cells(Ctr, 15).Value = "7911" And _
.Cells(Ctr, 10).Value = 4450 Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("UM7911").Rows(UM7911row)
UM7911row = UM7911row + 1
ElseIf .Cells(Ctr, 15).Value = "7941" And _
.Cells(Ctr, 10).Value = 4450 Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("UM7941").Rows(UM7941row)
UM7941row = UM7941row + 1
ElseIf .Cells(Ctr, 15).Value = "7941" And _
.Cells(Ctr, 10).Value = "" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("NOUM_7941").Rows(NOUM_7941row)
NOUM_7941row = NOUM_7941row + 1
ElseIf .Cells(Ctr, 15).Value = "7911" And _
.Cells(Ctr, 10).Value = "" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("NOUM_7911").Rows(NOUM_7911row)
NOUM_7911row = NOUM_7911row + 1
ElseIf .Cells(Ctr, 10).Value >= 1000 Or _
.Cells(Ctr, 10).Value Like "*/*" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("Voicemail_Migration").Rows(Voicemail_Migrationrow)
Voicemail_Migrationrow = Voicemail_Migrationrow + 1
ElseIf .Cells(Ctr, 10).Value Like "*/*" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("Telecommuters").Rows(Telecommutersrow)
Telecommutersrow = Telecommutersrow + 1
ElseIf .Cells(Ctr, 15).Value = "WP" And _
.Cells(Ctr, 9).Value Like "*_*" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("Confrm_WP").Rows(Confrm_WProw)
Confrm_WProw = Confrm_WProw + 1
ElseIf .Cells(Ctr, 14).Value = "CLEARONE" And _
.Cells(Ctr, 15).Value = "CLEARONE" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("Polycom").Rows(Polycomrow)
Polycomrow = Polycomrow + 1
ElseIf .Cells(Ctr, 14).Value = "2500" And _
.Cells(Ctr, 15).Value = "FAX" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("FAX").Rows(FAXrow)
FAXrow = FAXrow + 1
ElseIf .Cells(Ctr, 15).Value = "WP" And _
.Cells(Ctr, 9).Value = "Outside" Then
'Select row in BLDGMaster to copy
.Rows(Ctr).Copy Destination:=Worksheets("OpenArea").Rows(OpenArearow)
OpenArearow = OpenArearow + 1
End If
Next Ctr
End With
Application.CutCopyMode = False
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute: MsgBox "Data Not Found."
End Sub
Thanks for your help with this.
Jim