If there is a name on a sheet other than sheet 1, and it matches a name on
sheet1, colA, copy the contents of colB of that row to the sheet is matches?

I had asked this earlier and was given an answer, but the answer didn't do
anything.

Thanks for your assistanct and thanks if this isn't possible also.

I have an idea what it is you want to accomplish, But please explain more carefully what the specifics are. Choose clarity over being succinct.

Well, I have to provide different offices each day information. Not all
offices receive information. I produce a standard list of office through vb
on sheet1 and their address are located on column b of sheet1.

The following sheets (sheet2, sheet3, etc.) will be the variable offices.

What I want the macro to do is look at cell A1 of the sheet2, determine the
office, compare to sheet1 and choose the correct email address, insert a row
on sheet2 and paste that address -

loop to sheet3 and repeat from above.

I had originally written this on 4/14/04 as 12:20 p.m., and a fellow named
andoni replied, but the code provided did not do anything.

"chris" wrote in message
...
I have an idea what it is you want to accomplish, But please explain more
carefully what the specifics are. Choose clarity over being succinct.

My first post predated your response to chris. Is this
what you want?

Dim MainSheet As Worksheet
Dim Rng As Range, C As Range, CC As Range
Dim i As Integer
Dim ShtCount As Integer

Set MainSheet = Sheets(1)
ShtCount = ThisWorkbook.Sheets.Count
Set Rng = MainSheet.Columns(1). _
SpecialCells(xlCellTypeConstants)
For Each C In Rng.Cells
For i = 2 To ShtCount
Set CC = Sheets(i).Range("A1")
If Trim(C) = Trim(CC) Then
CC.Offset(1).Rows.Insert Shift:=xlDown
CC.Offset(1) = C.Offset(, 1)
End If
Next
Next
End Sub

Regards,
Greg

The following code demos my interpretation of the
mechanics of what you want.

Assumed is that the number of sheets are fixed, in this
case Sheet1 to Sheet4. And for each "name" on Sheet1 as
listed in Column A, you want to look for a match in all of
the other 3 sheets. If a match is found, then copy the
contents from the cell immediately to the right (Col. B)
of the cell in Sheet1 containing the particular "name".
Transfer this value to the cell immediately to the right
of the matching cell in the other worksheet (Just a guess
as to where you wanted it).

The term "name" is assumed to mean a person's name, city
name or such - not a named range. To test the code:
1) Ensure that the workbook has at least four worksheets.
2) In Column A of Sheet1 enter the list of names. You can
leave gaps between them.
3) Also, enter in Col. B a unique value opposite each
name.
4) In the other sheets, randomly enter text values, the
majority NOT being one of the names in Sheet1 but with one
or more matching one or more of the names.
4) Run the code.
5) Let us know to what extent this interpretation is
incorrect. Note that it worked for me instantaneously when
I tested it.

Sub XYZ()
Dim MainSheet As Worksheet, WS As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim C As Range, CC As Range
Dim i As Integer, ShtArr As Variant
Dim FirstAdd As String

On Error Resume Next
Set MainSheet = Sheets(1)
ShtArr = Array(Sheets(2), Sheets(3), Sheets(4))
Set Rng1 = MainSheet.Columns(1).SpecialCells
(xlCellTypeConstants)
For Each C In Rng1.Cells
For i = 0 To 2
Set Rng2 = ShtArr(i).UsedRange.SpecialCells
(xlCellTypeConstants)
Set CC = Rng2.Find(What:=C.Value, LookIn:=xlValues)
If Not CC Is Nothing Then
FirstAdd = CC.Address
CC.Offset(, 1) = C.Offset(, 1)
Do While Not CC.Address = FirstAdd
Set CC = Rng2.FindNext
If Not CC Is Nothing Then CC.Offset(, 1) = C.Offset(, 1)
Loop
End If
Next
Next
On Error GoTo 0
End Sub

Regards,
Greg
(VBA amateur)

-----Original Message-----
If there is a name on a sheet other than sheet 1, and it
matches a name on
sheet1, colA, copy the contents of colB of that row to
the sheet is matches?

I had asked this earlier and was given an answer, but the
answer didn't do
anything.

Thanks for your assistanct and thanks if this isn't
possible also.


.

Getting closer and it was performing as expected. It would enter on line
two, col b the address found on sheet1 that matched the name. However, there
was information on col b that was being covered up or replaced. I was
hoping to add a ro and insert the address so no information was destroyed
.... is this possible? (Maybe a different method should be used, but I was
hoping to land that address in cell A1 to send out as an email later using
Ron De bruin's mail code)

I have up to 22 offices and I tried to 'monkey' with the code to accomodate
this and increase the number of sheets, but it ignore all but four sheets.

Thanks for getting me this close and any other suggestions.


"Greg Wilson" wrote in message
...
The following code demos my interpretation of the
mechanics of what you want.

Assumed is that the number of sheets are fixed, in this
case Sheet1 to Sheet4. And for each "name" on Sheet1 as
listed in Column A, you want to look for a match in all of
the other 3 sheets. If a match is found, then copy the
contents from the cell immediately to the right (Col. B)
of the cell in Sheet1 containing the particular "name".
Transfer this value to the cell immediately to the right
of the matching cell in the other worksheet (Just a guess
as to where you wanted it).

The term "name" is assumed to mean a person's name, city
name or such - not a named range. To test the code:
1) Ensure that the workbook has at least four worksheets.
2) In Column A of Sheet1 enter the list of names. You can
leave gaps between them.
3) Also, enter in Col. B a unique value opposite each
name.
4) In the other sheets, randomly enter text values, the
majority NOT being one of the names in Sheet1 but with one
or more matching one or more of the names.
4) Run the code.
5) Let us know to what extent this interpretation is
incorrect. Note that it worked for me instantaneously when
I tested it.

Sub XYZ()
Dim MainSheet As Worksheet, WS As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim C As Range, CC As Range
Dim i As Integer, ShtArr As Variant
Dim FirstAdd As String

On Error Resume Next
Set MainSheet = Sheets(1)
ShtArr = Array(Sheets(2), Sheets(3), Sheets(4))
Set Rng1 = MainSheet.Columns(1).SpecialCells
(xlCellTypeConstants)
For Each C In Rng1.Cells
For i = 0 To 2
Set Rng2 = ShtArr(i).UsedRange.SpecialCells
(xlCellTypeConstants)
Set CC = Rng2.Find(What:=C.Value, LookIn:=xlValues)
If Not CC Is Nothing Then
FirstAdd = CC.Address
CC.Offset(, 1) = C.Offset(, 1)
Do While Not CC.Address = FirstAdd
Set CC = Rng2.FindNext
If Not CC Is Nothing Then CC.Offset(, 1) = C.Offset(, 1)
Loop
End If
Next
Next
On Error GoTo 0
End Sub

Regards,
Greg
(VBA amateur)

-----Original Message-----
If there is a name on a sheet other than sheet 1, and it
matches a name on
sheet1, colA, copy the contents of colB of that row to
the sheet is matches?

I had asked this earlier and was given an answer, but the
answer didn't do
anything.

Thanks for your assistanct and thanks if this isn't
possible also.


.

Did you try the code in my second post? It inserts a row
and shouldn't destroy any info and should also accomodate
any number of sheets. My first post was written before
your response to chris.

A simple solution, perhaps, is to simply use the VLookUp
worksheet formula: "=VLOOKUP(A1, Sheet1!A1:B22, 2)".
Paste this to Cell A2 in all of the 22 sheets containing
the office names. You will have to structure your
worksheets to accomodate the formula as it will be
permanent. Be advised that I'm not familiar with Ron De
bruin's mail code.

Regards,
Greg


-----Original Message-----
Getting closer and it was performing as expected. It
would enter on line
two, col b the address found on sheet1 that matched the
name. However, there
was information on col b that was being covered up or
replaced. I was
hoping to add a ro and insert the address so no
information was destroyed
.... is this possible? (Maybe a different method should
be used, but I was
hoping to land that address in cell A1 to send out as an
email later using
Ron De bruin's mail code)

I have up to 22 offices and I tried to 'monkey' with the
code to accomodate
this and increase the number of sheets, but it ignore all
but four sheets.

Thanks for getting me this close and any other
suggestions.


"Greg Wilson" wrote
in message
...
The following code demos my interpretation of the
mechanics of what you want.

Assumed is that the number of sheets are fixed, in this
case Sheet1 to Sheet4. And for each "name" on Sheet1 as
listed in Column A, you want to look for a match in all
of
the other 3 sheets. If a match is found, then copy the
contents from the cell immediately to the right (Col. B)
of the cell in Sheet1 containing the particular "name".
Transfer this value to the cell immediately to the right
of the matching cell in the other worksheet (Just a
guess
as to where you wanted it).

The term "name" is assumed to mean a person's name, city
name or such - not a named range. To test the code:
1) Ensure that the workbook has at least four
worksheets.
2) In Column A of Sheet1 enter the list of names. You
can
leave gaps between them.
3) Also, enter in Col. B a unique value opposite each
name.
4) In the other sheets, randomly enter text values, the
majority NOT being one of the names in Sheet1 but with
one
or more matching one or more of the names.
4) Run the code.
5) Let us know to what extent this interpretation is
incorrect. Note that it worked for me instantaneously
when
I tested it.

Sub XYZ()
Dim MainSheet As Worksheet, WS As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim C As Range, CC As Range
Dim i As Integer, ShtArr As Variant
Dim FirstAdd As String

On Error Resume Next
Set MainSheet = Sheets(1)
ShtArr = Array(Sheets(2), Sheets(3), Sheets(4))
Set Rng1 = MainSheet.Columns(1).SpecialCells
(xlCellTypeConstants)
For Each C In Rng1.Cells
For i = 0 To 2
Set Rng2 = ShtArr(i).UsedRange.SpecialCells
(xlCellTypeConstants)
Set CC = Rng2.Find(What:=C.Value, LookIn:=xlValues)
If Not CC Is Nothing Then
FirstAdd = CC.Address
CC.Offset(, 1) = C.Offset(, 1)
Do While Not CC.Address = FirstAdd
Set CC = Rng2.FindNext
If Not CC Is Nothing Then CC.Offset(, 1) = C.Offset(, 1)
Loop
End If
Next
Next
On Error GoTo 0
End Sub

Regards,
Greg
(VBA amateur)

-----Original Message-----
If there is a name on a sheet other than sheet 1, and
it
matches a name on
sheet1, colA, copy the contents of colB of that row to
the sheet is matches?

I had asked this earlier and was given an answer, but
the
answer didn't do
anything.

Thanks for your assistanct and thanks if this isn't
possible also.


.



.

Well I have worked with this for two hours and couldn't get it to work. The
second post code wouldn't run anything .. nothing would change. Did you get
it to do something when you tested it? As for the VLook up, there is a code
that creates these sheets every day and the end user wouldn't have the time
to insert this. I think this can work as you have already proven it works
on four sheets .. now can we get it follow the thought pattern of what we
want!

I'd be happy to share the a sample spreadsheet for testing. Also, do you
think there is an addin I forgot to have turned on and maybe that's why the
second post didn't make any changes? I copy and pasted directly from the
ng. Thanks for taking the time to follow up on this post.

"Greg Wilson" wrote in message
...
Did you try the code in my second post? It inserts a row
and shouldn't destroy any info and should also accomodate
any number of sheets. My first post was written before
your response to chris.

A simple solution, perhaps, is to simply use the VLookUp
worksheet formula: "=VLOOKUP(A1, Sheet1!A1:B22, 2)".
Paste this to Cell A2 in all of the 22 sheets containing
the office names. You will have to structure your
worksheets to accomodate the formula as it will be
permanent. Be advised that I'm not familiar with Ron De
bruin's mail code.

Regards,
Greg


-----Original Message-----
Getting closer and it was performing as expected. It
would enter on line
two, col b the address found on sheet1 that matched the
name. However, there
was information on col b that was being covered up or
replaced. I was
hoping to add a ro and insert the address so no
information was destroyed
.... is this possible? (Maybe a different method should
be used, but I was
hoping to land that address in cell A1 to send out as an
email later using
Ron De bruin's mail code)

I have up to 22 offices and I tried to 'monkey' with the
code to accomodate
this and increase the number of sheets, but it ignore all
but four sheets.

Thanks for getting me this close and any other
suggestions.


"Greg Wilson" wrote
in message
...
The following code demos my interpretation of the
mechanics of what you want.

Assumed is that the number of sheets are fixed, in this
case Sheet1 to Sheet4. And for each "name" on Sheet1 as
listed in Column A, you want to look for a match in all
of
the other 3 sheets. If a match is found, then copy the
contents from the cell immediately to the right (Col. B)
of the cell in Sheet1 containing the particular "name".
Transfer this value to the cell immediately to the right
of the matching cell in the other worksheet (Just a
guess
as to where you wanted it).

The term "name" is assumed to mean a person's name, city
name or such - not a named range. To test the code:
1) Ensure that the workbook has at least four
worksheets.
2) In Column A of Sheet1 enter the list of names. You
can
leave gaps between them.
3) Also, enter in Col. B a unique value opposite each
name.
4) In the other sheets, randomly enter text values, the
majority NOT being one of the names in Sheet1 but with
one
or more matching one or more of the names.
4) Run the code.
5) Let us know to what extent this interpretation is
incorrect. Note that it worked for me instantaneously
when
I tested it.

Sub XYZ()
Dim MainSheet As Worksheet, WS As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim C As Range, CC As Range
Dim i As Integer, ShtArr As Variant
Dim FirstAdd As String

On Error Resume Next
Set MainSheet = Sheets(1)
ShtArr = Array(Sheets(2), Sheets(3), Sheets(4))
Set Rng1 = MainSheet.Columns(1).SpecialCells
(xlCellTypeConstants)
For Each C In Rng1.Cells
For i = 0 To 2
Set Rng2 = ShtArr(i).UsedRange.SpecialCells
(xlCellTypeConstants)
Set CC = Rng2.Find(What:=C.Value, LookIn:=xlValues)
If Not CC Is Nothing Then
FirstAdd = CC.Address
CC.Offset(, 1) = C.Offset(, 1)
Do While Not CC.Address = FirstAdd
Set CC = Rng2.FindNext
If Not CC Is Nothing Then CC.Offset(, 1) = C.Offset(, 1)
Loop
End If
Next
Next
On Error GoTo 0
End Sub

Regards,
Greg
(VBA amateur)

-----Original Message-----
If there is a name on a sheet other than sheet 1, and
it
matches a name on
sheet1, colA, copy the contents of colB of that row to
the sheet is matches?

I had asked this earlier and was given an answer, but
the
answer didn't do
anything.

Thanks for your assistanct and thanks if this isn't
possible also.


.



.

For my test using the second code offering I set the
workbook up as follows:

In Sheet1 I entered the following city names and adjacent
text values intended to represent email
addresses. "Chicago" in Cell A1 and "abc" in Cell B1 and
the remainder immediately below as shown:

COL. A__________COL. B
Chicago___________abc
Paris_____________def
Montreal__________ghi
Portland__________jkl
Toronto___________mno
Denver____________pqr
Washington________stu
Dallas____________vwx
Halifax___________yz

In Sheet2 in Cell A1 I entered "Chicago" and put an "x"
immediately below in Cell A2. This was to show that the
data in this cell does not get overwritten. I then
entered "Toronto" in Sheet3 (Cell A1) and put an "x" below
it also. I continued this for 7 sheets putting different
names in Cell A1 of each and an "x" immediately below the
names. The code is designed to handle as many sheets as
there are in the workbook so the number 7 is irrelevant.

Results were as follows:

<Sheet2
Cell A1: "Chicago"
Cell A2: "abc"
Cell A3" "x"

<Sheet3
Cell A1: "Toronto"
Cell A2: "def"
Cell A3" "x"

<Sheet4
Cell A1: "Montreal"
Cell A2: "ghi"
Cell A3: "x"

<Sheet5
Cell A1: "Halifax"
Cell A2: "yz"
Cell A3: "x"

<Sheet6
Cell A1: "Portland"
Cell A2: "jkl"
Cell A3: "x"

<Sheet7
Cell A1: "Dallas"
Cell A2: "vwx"
Cell A3: "x"

The above was my understanding of what you were looking
for. I suspect that your workbook is set up different and
that is why it didn't work. My first code offering was
far more general and looks anywhere in the used range of
the other sheets for the names. I believe that is why you
had some success.

Let me know how the mechanics of my test is different from
your needs.

Regards,
Greg

PS: I am aware of a syntax error in my first code
offering. I will correct it if we end up using it.