Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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.
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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. . . |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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! |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
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! |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maybe this isn't possible to match name and copy?
'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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match with copy | Excel Discussion (Misc queries) | |||
Match and copy | Excel Worksheet Functions | |||
Compare Col A and Col M, if Match, Copy Col N to Col E | Excel Worksheet Functions | |||
Match and Copy | Excel Worksheet Functions | |||
Copy to next empty row, if not a match | Excel Discussion (Misc queries) |