What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

Try the macro below. Change the Const statements as necessary. You didn't
give enough details to write specific code for your application.


Sub GetTerminations()
Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"

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)
.Range(SumFirstName & SumRowCount) = FirstName
.Range(SumLastName & SumRowCount) = LastName
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
End With

End Sub

"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

What info would you need me to provide so we can get specific?
--
thanks in advance


"Joel" wrote:

Try the macro below. Change the Const statements as necessary. You didn't
give enough details to write specific code for your application.


Sub GetTerminations()
Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"

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)
.Range(SumFirstName & SumRowCount) = FirstName
.Range(SumLastName & SumRowCount) = LastName
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
End With

End Sub

"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

The sheet names and columns. but all you have to do is change the Const
statements below.

Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"



"jcontrer" wrote:

What info would you need me to provide so we can get specific?
--
thanks in advance


"Joel" wrote:

Try the macro below. Change the Const statements as necessary. You didn't
give enough details to write specific code for your application.


Sub GetTerminations()
Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"

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)
.Range(SumFirstName & SumRowCount) = FirstName
.Range(SumLastName & SumRowCount) = LastName
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
End With

End Sub

"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

Terminated Employees is the name of the destination sheet
the columns a A:last, B:first, C:language, D:qualified, E:general,
F:hospital, G:department, H:extension, I:comments, J:interested, K:fluency,
L:medical, M:shift, N:special, O:bank
Employee Bi-Lingual Skills is the name of the second sheet
It has exactly the same columns as the Terminated Employees sheet
New Terminations is the name of the third sheet
It has two columns A:Last and B:First

So when i insert this macro, it will search for the first and last name, and
if a match is found, the whole row would be taken out and put into the
terminated employees sheet? great!


--
thanks in advance


"Joel" wrote:

The sheet names and columns. but all you have to do is change the Const
statements below.

Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"



"jcontrer" wrote:

What info would you need me to provide so we can get specific?
--
thanks in advance


"Joel" wrote:

Try the macro below. Change the Const statements as necessary. You didn't
give enough details to write specific code for your application.


Sub GetTerminations()
Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"

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)
.Range(SumFirstName & SumRowCount) = FirstName
.Range(SumLastName & SumRowCount) = LastName
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
End With

End Sub

"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

Sub GetTerminations()
Const SumSheet = "summary"
Const BilingualSheet = "Employee Bi-Lingual Skills"
Const TermSheet = "New Terminations"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"

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)
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
End With

End Sub
"jcontrer" wrote:

Terminated Employees is the name of the destination sheet
the columns a A:last, B:first, C:language, D:qualified, E:general,
F:hospital, G:department, H:extension, I:comments, J:interested, K:fluency,
L:medical, M:shift, N:special, O:bank
Employee Bi-Lingual Skills is the name of the second sheet
It has exactly the same columns as the Terminated Employees sheet
New Terminations is the name of the third sheet
It has two columns A:Last and B:First

So when i insert this macro, it will search for the first and last name, and
if a match is found, the whole row would be taken out and put into the
terminated employees sheet? great!


--
thanks in advance


"Joel" wrote:

The sheet names and columns. but all you have to do is change the Const
statements below.

Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"



"jcontrer" wrote:

What info would you need me to provide so we can get specific?
--
thanks in advance


"Joel" wrote:

Try the macro below. Change the Const statements as necessary. You didn't
give enough details to write specific code for your application.


Sub GetTerminations()
Const SumSheet = "summary"
Const BilingualSheet = "bilingual employees"
Const TermSheet = "termination"
Const TermLastName = "A"
Const TermFirstName = "B"
Const BiLastName = "A"
Const BiFirstName = "B"
Const SumLastName = "A"
Const SumFirstName = "B"

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)
.Range(SumFirstName & SumRowCount) = FirstName
.Range(SumLastName & SumRowCount) = LastName
SumRowCount = SumRowCount + 1
End With
Exit Do
End If
BiRowCount = BiRowCount + 1
Loop
End With
TermRowCount = TermRowCount + 1
Loop
End With

End Sub

"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

These are the Sheet and Column names. Please update the macro beacuse i dont
know what to put where.

sheet 1: Employee Bi-Lingual Skills
Columns: A:LAST B:FIRST C:LANGUAGE D:QUALIFIED E:GENERAL F:HOSPITAL
G:DEPARTMENT H:EXTENTION I:COMMENTS J:INTERESTED K:FLUENCY L:MEDICAL M:SHIFT
N:SPECIAL O:BANK

Sheet 2: Terminated Employees
Columns: same as sheet 1

Sheet 3: New Terminations
Columns: A:Last B:First

So this Macro will search in sheet 1 to see if any names match sheet 3, and
if it then finds a match, it will cut the entire row out of sheet 1 and paste
it on sheet 2?


--
thanks in advance


"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance

I don't need these because I'm copying the entire row.

"jcontrer" wrote:

These are the Sheet and Column names. Please update the macro beacuse i dont
know what to put where.

sheet 1: Employee Bi-Lingual Skills
Columns: A:LAST B:FIRST C:LANGUAGE D:QUALIFIED E:GENERAL F:HOSPITAL
G:DEPARTMENT H:EXTENTION I:COMMENTS J:INTERESTED K:FLUENCY L:MEDICAL M:SHIFT
N:SPECIAL O:BANK

Sheet 2: Terminated Employees
Columns: same as sheet 1

Sheet 3: New Terminations
Columns: A:Last B:First

So this Macro will search in sheet 1 to see if any names match sheet 3, and
if it then finds a match, it will cut the entire row out of sheet 1 and paste
it on sheet 2?


--
thanks in advance


"jcontrer" wrote:

What i have:
I have three different spreadsheets; one with all of the bilingual employees
of a company and their qualifications, another is a list of all the
terminations for that company, which i get monthly but i have a backlog
because no one has done it in the past three years (which is a substantial
number) and the last is a spreadsheet which is now blank but with the same
format as the first spreadsheet mentioned.

What i want to do with what i have:
I want to use the second sheet mentioned earlier as the search criteria (i
want to search by first and last name) and i want to search for names in the
rows of the bilingual sheet that match the first and last name of someone in
the terminations sheet and then have the row in sheet 1 marked (maybe
highlighted) so i can cut the name out of the bilingual sheet (or it can be
cut out of the first sheet sheet and pasted to the third sheet automatically,
which would be easier). and put it in the third sheet which will serve as a
storage sheet for terminated employees so that if we get sued i'd still have
that persons qualifications information to cover me.


--
thanks in advance