J
jcontrer
I have this Macro:
Sub GetTerminations()
Const SumSheet = "entered on lawson"
Const BilingualSheet = "Employee Bi-Lingual Skills"
Const TermSheet = "Lawson employee list"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"
Dim rngToDelete As Range
Dim SumRowCount As Long
Dim TermRowCount As Long
Dim BiRowCount As Long
Dim FirstName As String
Dim LastName As String
SumRowCount = 1
TermRowCount = 1
With Sheets(TermSheet)
Do While .Range(TermLastName & TermRowCount) <> ""
LastName = .Range(TermLastName & TermRowCount)
FirstName = .Range(TermFirstName & TermRowCount)
With Sheets(BilingualSheet)
BiRowCount = 1
Do While .Range(BiLastName & BiRowCount) <> ""
If (.Range(BiLastName & BiRowCount) = LastName) And _
(.Range(BiFirstName & BiRowCount) = FirstName) Then
With Sheets(SumSheet)
Sheets(BilingualSheet).Rows(BiRowCount).Copy _
Destination:=.Rows(SumRowCount)
If rngToDelete Is Nothing Then
Set rngToDelete = _
Sheets(BilingualSheet).Rows(BiRowCount)
Else
Set rngToDelete = Union(rngToDelete, _
Sheets(BilingualSheet).Rows(BiRowCount))
End If
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
If Not rngToDelete Is Nothing Then rngToDelete.Delete
End With
End Sub
It is supposed to look for names in the termsheet that match those in the
bilingual sheet and then cut them from the bilingual sheet and paste them
onto the sumsheet.
My issue is:
The termsheet has 1233 names, all of which should be found (some with more
than one entry) and cut from the bilingual sheet and pasted onto the
sumsheet. so i should have at least 1233 entries in the sumsheet after i run
the macro. but that is not the case, i have substantially less, when i should
have substantially more (because of the duplicate entries.) what is wrong and
how can it be fixed? please be specific because as you can see i am not too
good at this.
Sub GetTerminations()
Const SumSheet = "entered on lawson"
Const BilingualSheet = "Employee Bi-Lingual Skills"
Const TermSheet = "Lawson employee list"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"
Dim rngToDelete As Range
Dim SumRowCount As Long
Dim TermRowCount As Long
Dim BiRowCount As Long
Dim FirstName As String
Dim LastName As String
SumRowCount = 1
TermRowCount = 1
With Sheets(TermSheet)
Do While .Range(TermLastName & TermRowCount) <> ""
LastName = .Range(TermLastName & TermRowCount)
FirstName = .Range(TermFirstName & TermRowCount)
With Sheets(BilingualSheet)
BiRowCount = 1
Do While .Range(BiLastName & BiRowCount) <> ""
If (.Range(BiLastName & BiRowCount) = LastName) And _
(.Range(BiFirstName & BiRowCount) = FirstName) Then
With Sheets(SumSheet)
Sheets(BilingualSheet).Rows(BiRowCount).Copy _
Destination:=.Rows(SumRowCount)
If rngToDelete Is Nothing Then
Set rngToDelete = _
Sheets(BilingualSheet).Rows(BiRowCount)
Else
Set rngToDelete = Union(rngToDelete, _
Sheets(BilingualSheet).Rows(BiRowCount))
End If
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
If Not rngToDelete Is Nothing Then rngToDelete.Delete
End With
End Sub
It is supposed to look for names in the termsheet that match those in the
bilingual sheet and then cut them from the bilingual sheet and paste them
onto the sumsheet.
My issue is:
The termsheet has 1233 names, all of which should be found (some with more
than one entry) and cut from the bilingual sheet and pasted onto the
sumsheet. so i should have at least 1233 entries in the sumsheet after i run
the macro. but that is not the case, i have substantially less, when i should
have substantially more (because of the duplicate entries.) what is wrong and
how can it be fixed? please be specific because as you can see i am not too
good at this.