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.

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.


.

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

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.

Oops...

Toronto should have been "mno" in Cell B1 of Sheet3. Typo
not code error.

Regards,
Greg

-----Original Message-----
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.

.

Okay, now this is just getting strange. I copied everything verbatim as you
instructed and thought if this worked ... I could rework the code that comes
before this. Well, needless to say ... the second code that you indicated
for me to use along with the sample provided for layout refused to run past
sheet 4 ... this is just too bizarre. Obviously something is wrong here ...
I copied the code again and even closed Excel to be sure ... I'm using Excel
2000/with Win2000.

I didn't even monkey with the code .. it is exactly as you wrote it and it
refuses to go past sheet4. If you had your sample working .. .maybe it is
this notebook I'm using ... it's a .. neva mind .. doubt it is that (just
getting frustrated!). There something I'm missing here!

Yours did run past sheet4 .. yes? And you didn't change anything in the code
from your original post? I wonder why mine won't work like yours ... and
the list on sheet1 is not even in alpha order (mine is going to be in the
final working code).

Thanks again for not giving up.

this code

ShtCount = ThisWorkbook.Sheets.Count
For Each C In Rng.Cells
For i = 2 To ShtCount


should go past Sheet 4

this code
For i = 0 To 2
Set Rng2 = ShtArr(i).UsedRange.SpecialCells

looks at 3 sheets. for a total of 4

--
Regards,
Tom Ogilvy


"Annette" wrote in message
...
Okay, now this is just getting strange. I copied everything verbatim as
you
instructed and thought if this worked ... I could rework the code that
comes
before this. Well, needless to say ... the second code that you indicated
for me to use along with the sample provided for layout refused to run
past
sheet 4 ... this is just too bizarre. Obviously something is wrong here
....
I copied the code again and even closed Excel to be sure ... I'm using
Excel
2000/with Win2000.

I didn't even monkey with the code .. it is exactly as you wrote it and it
refuses to go past sheet4. If you had your sample working .. .maybe it is
this notebook I'm using ... it's a .. neva mind .. doubt it is that (just
getting frustrated!). There something I'm missing here!

Yours did run past sheet4 .. yes? And you didn't change anything in the
code
from your original post? I wonder why mine won't work like yours ... and
the list on sheet1 is not even in alpha order (mine is going to be in the
final working code).

Thanks again for not giving up.

Annette,

The code below was the second code offering and is the one
that I suggested you use. This should definately go past
Sheet4 assuming you have more than 4 sheets. The first
(longer code) should only go to Sheet4 - it is designed to
do this. Tom Ogilvy's comment confirmed this. Are you
saying that this code only goes to Sheet4 ??? Also, the
code below won't do anything unless it finds a match in
Cell A1 of each sheet. Are you sure the names are in Cell
A1 of each sheet ???

My first code offering was designed to look anywhere in
the used range of each sheet instead of just Cell A1.
Therefore, it could find a match if the name was not in
A1. That is why I suspect you had some success with that
code. That code also has a flaw I am aware of. I won't
bother to fix it unless we end up using it.

Please confirm if you were using the code below and if the
names are in Cell A1 of each sheet (except Sheet1).

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

-----Original Message-----
Okay, now this is just getting strange. I copied
everything verbatim as you
instructed and thought if this worked ... I could rework
the code that comes
before this. Well, needless to say ... the second code
that you indicated
for me to use along with the sample provided for layout
refused to run past
sheet 4 ... this is just too bizarre. Obviously
something is wrong here ...
I copied the code again and even closed Excel to be
sure ... I'm using Excel
2000/with Win2000.

I didn't even monkey with the code .. it is exactly as
you wrote it and it
refuses to go past sheet4. If you had your sample
working .. .maybe it is
this notebook I'm using ... it's a .. neva mind .. doubt
it is that (just
getting frustrated!). There something I'm missing here!

Yours did run past sheet4 .. yes? And you didn't change
anything in the code
from your original post? I wonder why mine won't work
like yours ... and
the list on sheet1 is not even in alpha order (mine is
going to be in the
final working code).

Thanks again for not giving up.


.

I was using the first one ... and when I changed the numbers, it would run
more sheets.

When I use this (second and what you want me to test) against your example,
nothing happens. I recopied the code into a new name so I had the same code
and checked the names you provided. The second code runs but there are no
changes.

Thanks again for staying with this ... I am pretty sure what you have here
is what I need.

"Greg Wilson" wrote in message
...
Annette,

The code below was the second code offering and is the one
that I suggested you use. This should definately go past
Sheet4 assuming you have more than 4 sheets. The first
(longer code) should only go to Sheet4 - it is designed to
do this. Tom Ogilvy's comment confirmed this. Are you
saying that this code only goes to Sheet4 ??? Also, the
code below won't do anything unless it finds a match in
Cell A1 of each sheet. Are you sure the names are in Cell
A1 of each sheet ???

My first code offering was designed to look anywhere in
the used range of each sheet instead of just Cell A1.
Therefore, it could find a match if the name was not in
A1. That is why I suspect you had some success with that
code. That code also has a flaw I am aware of. I won't
bother to fix it unless we end up using it.

Please confirm if you were using the code below and if the
names are in Cell A1 of each sheet (except Sheet1).

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

-----Original Message-----
Okay, now this is just getting strange. I copied
everything verbatim as you
instructed and thought if this worked ... I could rework
the code that comes
before this. Well, needless to say ... the second code
that you indicated
for me to use along with the sample provided for layout
refused to run past
sheet 4 ... this is just too bizarre. Obviously
something is wrong here ...
I copied the code again and even closed Excel to be
sure ... I'm using Excel
2000/with Win2000.

I didn't even monkey with the code .. it is exactly as
you wrote it and it
refuses to go past sheet4. If you had your sample
working .. .maybe it is
this notebook I'm using ... it's a .. neva mind .. doubt
it is that (just
getting frustrated!). There something I'm missing here!

Yours did run past sheet4 .. yes? And you didn't change
anything in the code
from your original post? I wonder why mine won't work
like yours ... and
the list on sheet1 is not even in alpha order (mine is
going to be in the
final working code).

Thanks again for not giving up.


.

Annette,

I'm at a loss at this point. I'm willing to have a look
at it if you email the workbook. Make sure the VBE and
worksheets are all unprotected. As I said in my first
post, I'm just a VBA amateur. I also work for a living
and have yet to start on my income tax which is due the
end of the month. So no promises.

None the less, at least normally, this should be easy. I
suspect at this point it has to do with how the sheets are
added to the workbook or a sheet referencing issue; i.e.
Sheet1 is not necessarily the same as Sheets("Sheet1").

Greg Wilson



-----Original Message-----
I was using the first one ... and when I changed the
numbers, it would run
more sheets.

When I use this (second and what you want me to test)
against your example,
nothing happens. I recopied the code into a new name so
I had the same code
and checked the names you provided. The second code runs
but there are no
changes.

Thanks again for staying with this ... I am pretty sure
what you have here
is what I need.

"Greg Wilson" wrote
in message
...
Annette,

The code below was the second code offering and is the
one
that I suggested you use. This should definately go
past
Sheet4 assuming you have more than 4 sheets. The first
(longer code) should only go to Sheet4 - it is designed
to
do this. Tom Ogilvy's comment confirmed this. Are you
saying that this code only goes to Sheet4 ??? Also, the
code below won't do anything unless it finds a match in
Cell A1 of each sheet. Are you sure the names are in
Cell
A1 of each sheet ???

My first code offering was designed to look anywhere in
the used range of each sheet instead of just Cell A1.
Therefore, it could find a match if the name was not in
A1. That is why I suspect you had some success with
that
code. That code also has a flaw I am aware of. I won't
bother to fix it unless we end up using it.

Please confirm if you were using the code below and if
the
names are in Cell A1 of each sheet (except Sheet1).

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

-----Original Message-----
Okay, now this is just getting strange. I copied
everything verbatim as you
instructed and thought if this worked ... I could
rework
the code that comes
before this. Well, needless to say ... the second code
that you indicated
for me to use along with the sample provided for layout
refused to run past
sheet 4 ... this is just too bizarre. Obviously
something is wrong here ...
I copied the code again and even closed Excel to be
sure ... I'm using Excel
2000/with Win2000.

I didn't even monkey with the code .. it is exactly as
you wrote it and it
refuses to go past sheet4. If you had your sample
working .. .maybe it is
this notebook I'm using ... it's a .. neva mind ..
doubt
it is that (just
getting frustrated!). There something I'm missing
here!

Yours did run past sheet4 .. yes? And you didn't change
anything in the code
from your original post? I wonder why mine won't work
like yours ... and
the list on sheet1 is not even in alpha order (mine is
going to be in the
final working code).

Thanks again for not giving up.


.



.

I just got it work .. I moved the code from my personal.xls to the text.xls
spreadsheet so it was running inside the spreadsheet and it runs just
perfect! I had the code written into my personal.xls so that I could
access from any worksheet, instead of the test spreadsheet. How would I
change it so it would work from any personal.xls?

As I had indicated earlier this was going to be tagged on to another bit of
code that creates a workbook from another program.

This code is better than expected also. After I figure out what was wrong,
I was wowed!

"Greg Wilson" wrote in message
...
Annette,

I'm at a loss at this point. I'm willing to have a look
at it if you email the workbook. Make sure the VBE and
worksheets are all unprotected. As I said in my first
post, I'm just a VBA amateur. I also work for a living
and have yet to start on my income tax which is due the
end of the month. So no promises.

None the less, at least normally, this should be easy. I
suspect at this point it has to do with how the sheets are
added to the workbook or a sheet referencing issue; i.e.
Sheet1 is not necessarily the same as Sheets("Sheet1").

Greg Wilson



-----Original Message-----
I was using the first one ... and when I changed the
numbers, it would run
more sheets.

When I use this (second and what you want me to test)
against your example,
nothing happens. I recopied the code into a new name so
I had the same code
and checked the names you provided. The second code runs
but there are no
changes.

Thanks again for staying with this ... I am pretty sure
what you have here
is what I need.

"Greg Wilson" wrote
in message
...
Annette,

The code below was the second code offering and is the
one
that I suggested you use. This should definately go
past
Sheet4 assuming you have more than 4 sheets. The first
(longer code) should only go to Sheet4 - it is designed
to
do this. Tom Ogilvy's comment confirmed this. Are you
saying that this code only goes to Sheet4 ??? Also, the
code below won't do anything unless it finds a match in
Cell A1 of each sheet. Are you sure the names are in
Cell
A1 of each sheet ???

My first code offering was designed to look anywhere in
the used range of each sheet instead of just Cell A1.
Therefore, it could find a match if the name was not in
A1. That is why I suspect you had some success with
that
code. That code also has a flaw I am aware of. I won't
bother to fix it unless we end up using it.

Please confirm if you were using the code below and if
the
names are in Cell A1 of each sheet (except Sheet1).

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

-----Original Message-----
Okay, now this is just getting strange. I copied
everything verbatim as you
instructed and thought if this worked ... I could
rework
the code that comes
before this. Well, needless to say ... the second code
that you indicated
for me to use along with the sample provided for layout
refused to run past
sheet 4 ... this is just too bizarre. Obviously
something is wrong here ...
I copied the code again and even closed Excel to be
sure ... I'm using Excel
2000/with Win2000.

I didn't even monkey with the code .. it is exactly as
you wrote it and it
refuses to go past sheet4. If you had your sample
working .. .maybe it is
this notebook I'm using ... it's a .. neva mind ..
doubt
it is that (just
getting frustrated!). There something I'm missing
here!

Yours did run past sheet4 .. yes? And you didn't change
anything in the code
from your original post? I wonder why mine won't work
like yours ... and
the list on sheet1 is not even in alpha order (mine is
going to be in the
final working code).

Thanks again for not giving up.


.



.

Annette,

The "ThisWorkbook" statement references the workbook from
which the code is running; i.e. the Personal.xls workbook
in your case. Unless you changed it, it has only one
worksheet. Therefore, the statement:
"ShtCount = ThisWorkbook.Sheets.Count" sets the ShtCount
variable to 1. Therefore, the statement "For i = 2 To
ShtCount" won't do anything.

To fix it just change:
ShtCount = ThisWorkbook.Sheets.Count
To:
ShtCount = ActiveWorkbook.Sheets.Count

Works for me.

Regards,
Greg

-----Original Message-----
I just got it work .. I moved the code from my
personal.xls to the text.xls
spreadsheet so it was running inside the spreadsheet and
it runs just
perfect! I had the code written into my personal.xls
so that I could
access from any worksheet, instead of the test
spreadsheet. How would I
change it so it would work from any personal.xls?

As I had indicated earlier this was going to be tagged on
to another bit of
code that creates a workbook from another program.

This code is better than expected also. After I figure
out what was wrong,
I was wowed!

Okay, I think I am in business .. .it is working .. that was quite a workout
for you. Thank you for your time. Appreciate all you have contributed!

"Greg Wilson" wrote in message
...
Annette,

The "ThisWorkbook" statement references the workbook from
which the code is running; i.e. the Personal.xls workbook
in your case. Unless you changed it, it has only one
worksheet. Therefore, the statement:
"ShtCount = ThisWorkbook.Sheets.Count" sets the ShtCount
variable to 1. Therefore, the statement "For i = 2 To
ShtCount" won't do anything.

To fix it just change:
ShtCount = ThisWorkbook.Sheets.Count
To:
ShtCount = ActiveWorkbook.Sheets.Count

Works for me.

Regards,
Greg

-----Original Message-----
I just got it work .. I moved the code from my
personal.xls to the text.xls
spreadsheet so it was running inside the spreadsheet and
it runs just
perfect! I had the code written into my personal.xls
so that I could
access from any worksheet, instead of the test
spreadsheet. How would I
change it so it would work from any personal.xls?

As I had indicated earlier this was going to be tagged on
to another bit of
code that creates a workbook from another program.

This code is better than expected also. After I figure
out what was wrong,
I was wowed!

'sorry for the delay i have been out for a big while!


Sub AAA()
Dim Cell As Range
Dim Sht As Worksheet

Dim Rg As Range

'if you sheet with the proper email addresses are in a sheet
'named Peter
'change andoni for peter
'but in my computer works properly
Set Rg = Sheets("andoni").Range(Range("A1")
Range("A65536").End(xlUp))
For Each Cell In Rg
For Each Sht In ThisWorkbook.Sheets
If Cell.Value = Sht.Name Then
With Sht
.Visible = True
.Activate
.Rows(1).Insert
.Range("A1").Value = Cell.Offset(0, 1).Value
Exit For
End With
End If
Next Sht
Next Cell
End Su

--
Message posted from http://www.ExcelForum.com