Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Looping Macro€¦with AutoFilter
This is kind of difficult to explain, but here goes. Below I have a loop
(code is below) that filters items in AA1:AA11 and copies pastes the results into A3:A6, and loops through all items in the list of names in AA and it even assigns the persons name to the Worksheet tab. It is actually very nice, and I tip my hat to Joel for putting this together for me!! Now, the requirements have changed a bit and Id like to know if the following is possible€¦ Is there a way to modify this macro to apply the AutoFilter to ColumnAA, copy/paste the name into Range A3:A6 (this will always be the same), then turn off the filter, move to ColumnAB, apply the filter, and filter names in AB (these are the same names), and then copy the resulting items in AB:AC, and paste these in A11:B15 (copied down five times), then skip a row, and then go back to AB and get the next names in AB and the adjacent AC, and copy/paste these into lets say A17:B17 (remember A11:B11, then copied down 5 then skip a row). After Excel gets to the end of the list in AB (which is variable and always of different length) I would need it to turn off the filter in AB:AC and go to AD:AF and again apply the filter and copy/paste names in AD:AF and do the same as above, starting in the first unused cell in Column A (at this point I dont know how far down I will be in ColumnA). I know this is asking a heck of a lot. The looping stuff has always been tricky for me. Ive been working on this since early this morning. Ive tried many things in Access and Excel too (of course). Although it is very complex, I think this is the best, and most feasible, solution. However, if someone has a better solution I am open to suggestions!! Anyway, if someone here understands my need, PLEASE post back with what you think would be a solution, and Ill try to make some modifications if it doesnt work, and together, hopefully, we can get this thing working. Sub SheetsRVP() With Sheets("RVP") lstRw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each C In .Range("AA1:AA11") C.Copy ..Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) ..Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit Columns("K:K").Select Selection.ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & C Next C End With End Sub Regards, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Looping Macro.with AutoFilter
this code and description of what you want doesn't make any sense to me.
1.the code has a variable called lstrw, but it's not used anywhere. 2. you're using a range of AA1:AA11, but only copying one cell to a range of 4 cells. 3. then you're copying the same range of data to every sheet a1:O17. maybe this is what you want to do, i don't know. also 4. you want an autofilter on columns starting with "AA". but what criteria do you want to autofilter by? this is what i did with your posted code, i'll try to post an example of autofiltering in another post. Sub SheetsRVP() Dim lstrw As Long Dim c As Range Dim ws As Worksheet Dim newsht As Worksheet Set ws = Worksheets("RVP") With ws lstrw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each c In .Range("AA1:AA" & lstrw) c.Copy .Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Worksheets.Add(after:=Sheets(Sheets.Count)) .Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit newsht.Columns("K:K").ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & c Next End With End Sub -- Gary "ryguy7272" wrote in message ... This is kind of difficult to explain, but here goes. Below I have a loop (code is below) that filters items in AA1:AA11 and copies pastes the results into A3:A6, and loops through all items in the list of names in AA and it even assigns the person's name to the Worksheet tab. It is actually very nice, and I tip my hat to Joel for putting this together for me!! Now, the requirements have changed a bit and I'd like to know if the following is possible. Is there a way to modify this macro to apply the AutoFilter to ColumnAA, copy/paste the name into Range A3:A6 (this will always be the same), then turn off the filter, move to ColumnAB, apply the filter, and filter names in AB (these are the same names), and then copy the resulting items in AB:AC, and paste these in A11:B15 (copied down five times), then skip a row, and then go back to AB and get the next names in AB and the adjacent AC, and copy/paste these into let's say A17:B17 (remember A11:B11, then copied down 5 then skip a row). After Excel gets to the end of the list in AB (which is variable and always of different length) I would need it to turn off the filter in AB:AC and go to AD:AF and again apply the filter and copy/paste names in AD:AF and do the same as above, starting in the first unused cell in Column A (at this point I don't know how far down I will be in ColumnA). I know this is asking a heck of a lot. The looping stuff has always been tricky for me. I've been working on this since early this morning. I've tried many things in Access and Excel too (of course). Although it is very complex, I think this is the best, and most feasible, solution. However, if someone has a better solution I am open to suggestions!! Anyway, if someone here understands my need, PLEASE post back with what you think would be a solution, and I'll try to make some modifications if it doesn't work, and together, hopefully, we can get this thing working. Sub SheetsRVP() With Sheets("RVP") lstRw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each C In .Range("AA1:AA11") C.Copy .Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) .Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit Columns("K:K").Select Selection.ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & C Next C End With End Sub Regards, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Looping Macro.with AutoFilter
Thanks for taking the time to look at this Gary! Yes, this is a complex
scenario, and I dont really want to use Excel, or even Access, to do this task, but I think Excel can handle it, and I cant think of any other technology that would allow me to do what I need to do. Gary, your macro did the first part totally correct. It copied the names of all the VPs to all the appropriate places in all the sheets (from Column AA to Range A3:A6 in each sheet)! This worked great, and then I think it copied the last name in Column AB (the VP column) into the appropriate places on the €˜Todd sheet, but unfortunately it placed the name Todd in all of the VP sheets, and the accompanying Directors, that report to Todd, in all of the sheets. Instead, I wanted to filter the names in Column AB, and copy the names of the VPs in Column AB, with the appropriate Directors (which are in Column AC) that report to each VP, in each of the appropriate sheets. Let me try again to describe the scenario. Part #1) We have a hierarchy of VPs, Directors who report to the VPs, and Sales Reps who report to the Directors. In cell AA1 I have the word €˜Name just to have a heading to do the AutoFilter with. AA2:AA12 contains the names of the VPs. I want to filter for each name in this range and copy/paste each name from A3:A6, and then name each Sheet with the name of the person in this list. This is the easiest part and this part works fine. Part#2) We have the Directors who report to these VPs, and the VPs names appear in AB2:AB48. This last row, row 48, could change, so I wanted to use something like this: lstrw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each c In .Range("AA1:AA" & lstrw) Then in AC2:AC48, we have the names of the Directors that report to the VPs. I wanted to apply the filter on AB1, and filter for the first name in the list, to see the VP and the Directors that report to this VP. Then, for each name in Column AC, copy and paste the names into a space, perhaps 4 rows, below where the last loop stopped. This starting point will always be the same, and will be A11:B11. The VPs names will be in column A and the Directors names will appear in column B, five rows at a time. For instance, if David is the VP and the directors are Colleen, Conrad, and Garrett, then A11:A15 would be David, B11:B15 would be Colleen, skip a row, A17:A21 would be David, skip a row and B17:B21 would be Conrad, and A23:A27 would be David and B23:B27 would be Garrett. This is the end of the list in Column AC, then the macro would move to the next VP name in column AB, and continue the process over again, until all the names in Columns AB and AC have been passed through. Part#3) Then, to top it off, I wanted to try to do this again on Columns AD, AE, and AF. AD has the VPs names (same as before), AE has the Directors names (same as before), and AF has the Sales Reps names (the sales reps report to the Directors; this is the hierarchy). These would go under the rows where the part#2 part ended. Phew... The reason for all this is because we are doing some complex €˜lookups on a Pivot Table, using some =GETPIVOTDATA() functions. Ive got a handle on this part, I just cant get the looping thing figured out. At this point, I'm fairly certain that this is possible, but I think I am a little bit away from a practical solution. If you can post back with what you think is a reasonable solution, Id seriously appreciate it Gary!! If anyone else has an idea of how to do this, Id love to hear it!! Thanks so very much!! Ryan--- -- RyGuy "Gary Keramidas" wrote: this code and description of what you want doesn't make any sense to me. 1.the code has a variable called lstrw, but it's not used anywhere. 2. you're using a range of AA1:AA11, but only copying one cell to a range of 4 cells. 3. then you're copying the same range of data to every sheet a1:O17. maybe this is what you want to do, i don't know. also 4. you want an autofilter on columns starting with "AA". but what criteria do you want to autofilter by? this is what i did with your posted code, i'll try to post an example of autofiltering in another post. Sub SheetsRVP() Dim lstrw As Long Dim c As Range Dim ws As Worksheet Dim newsht As Worksheet Set ws = Worksheets("RVP") With ws lstrw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each c In .Range("AA1:AA" & lstrw) c.Copy .Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Worksheets.Add(after:=Sheets(Sheets.Count)) .Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit newsht.Columns("K:K").ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & c Next End With End Sub -- Gary "ryguy7272" wrote in message ... This is kind of difficult to explain, but here goes. Below I have a loop (code is below) that filters items in AA1:AA11 and copies pastes the results into A3:A6, and loops through all items in the list of names in AA and it even assigns the person's name to the Worksheet tab. It is actually very nice, and I tip my hat to Joel for putting this together for me!! Now, the requirements have changed a bit and I'd like to know if the following is possible. Is there a way to modify this macro to apply the AutoFilter to ColumnAA, copy/paste the name into Range A3:A6 (this will always be the same), then turn off the filter, move to ColumnAB, apply the filter, and filter names in AB (these are the same names), and then copy the resulting items in AB:AC, and paste these in A11:B15 (copied down five times), then skip a row, and then go back to AB and get the next names in AB and the adjacent AC, and copy/paste these into let's say A17:B17 (remember A11:B11, then copied down 5 then skip a row). After Excel gets to the end of the list in AB (which is variable and always of different length) I would need it to turn off the filter in AB:AC and go to AD:AF and again apply the filter and copy/paste names in AD:AF and do the same as above, starting in the first unused cell in Column A (at this point I don't know how far down I will be in ColumnA). I know this is asking a heck of a lot. The looping stuff has always been tricky for me. I've been working on this since early this morning. I've tried many things in Access and Excel too (of course). Although it is very complex, I think this is the best, and most feasible, solution. However, if someone has a better solution I am open to suggestions!! Anyway, if someone here understands my need, PLEASE post back with what you think would be a solution, and I'll try to make some modifications if it doesn't work, and together, hopefully, we can get this thing working. Sub SheetsRVP() With Sheets("RVP") lstRw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each C In .Range("AA1:AA11") C.Copy .Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) .Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit Columns("K:K").Select Selection.ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & C Next C End With End Sub Regards, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Looping Macro.with AutoFilter
can you email me the sheet with the names on it and i'll take a look at it.
either use the one posted or gkeramidasATcomcast.net. -- Gary "ryguy7272" wrote in message ... Thanks for taking the time to look at this Gary! Yes, this is a complex scenario, and I don't really want to use Excel, or even Access, to do this task, but I think Excel can handle it, and I can't think of any other technology that would allow me to do what I need to do. Gary, your macro did the first part totally correct. It copied the names of all the VPs to all the appropriate places in all the sheets (from Column AA to Range A3:A6 in each sheet)! This worked great, and then I think it copied the last name in Column AB (the VP column) into the appropriate places on the 'Todd' sheet, but unfortunately it placed the name Todd in all of the VP sheets, and the accompanying Directors, that report to Todd, in all of the sheets. Instead, I wanted to filter the names in Column AB, and copy the names of the VPs in Column AB, with the appropriate Directors (which are in Column AC) that report to each VP, in each of the appropriate sheets. Let me try again to describe the scenario. Part #1) We have a hierarchy of VPs, Directors who report to the VPs, and Sales Reps who report to the Directors. In cell AA1 I have the word 'Name' just to have a heading to do the AutoFilter with. AA2:AA12 contains the names of the VPs. I want to filter for each name in this range and copy/paste each name from A3:A6, and then name each Sheet with the name of the person in this list. This is the easiest part and this part works fine. Part#2) We have the Directors who report to these VPs, and the VPs names appear in AB2:AB48. This last row, row 48, could change, so I wanted to use something like this: lstrw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each c In .Range("AA1:AA" & lstrw) Then in AC2:AC48, we have the names of the Directors that report to the VPs. I wanted to apply the filter on AB1, and filter for the first name in the list, to see the VP and the Directors that report to this VP. Then, for each name in Column AC, copy and paste the names into a space, perhaps 4 rows, below where the last loop stopped. This starting point will always be the same, and will be A11:B11. The VPs names will be in column A and the Directors names will appear in column B, five rows at a time. For instance, if David is the VP and the directors are Colleen, Conrad, and Garrett, then A11:A15 would be David, B11:B15 would be Colleen, skip a row, A17:A21 would be David, skip a row and B17:B21 would be Conrad, and A23:A27 would be David and B23:B27 would be Garrett. This is the end of the list in Column AC, then the macro would move to the next VP name in column AB, and continue the process over again, until all the names in Columns AB and AC have been passed through. Part#3) Then, to top it off, I wanted to try to do this again on Columns AD, AE, and AF. AD has the VPs names (same as before), AE has the Director's names (same as before), and AF has the Sales Rep's names (the sales reps report to the Directors; this is the hierarchy). These would go under the rows where the part#2 part ended. Phew... The reason for all this is because we are doing some complex 'lookups' on a Pivot Table, using some =GETPIVOTDATA() functions. I've got a handle on this part, I just can't get the looping thing figured out. At this point, I'm fairly certain that this is possible, but I think I am a little bit away from a practical solution. If you can post back with what you think is a reasonable solution, I'd seriously appreciate it Gary!! If anyone else has an idea of how to do this, I'd love to hear it!! Thanks so very much!! Ryan--- -- RyGuy "Gary Keramidas" wrote: this code and description of what you want doesn't make any sense to me. 1.the code has a variable called lstrw, but it's not used anywhere. 2. you're using a range of AA1:AA11, but only copying one cell to a range of 4 cells. 3. then you're copying the same range of data to every sheet a1:O17. maybe this is what you want to do, i don't know. also 4. you want an autofilter on columns starting with "AA". but what criteria do you want to autofilter by? this is what i did with your posted code, i'll try to post an example of autofiltering in another post. Sub SheetsRVP() Dim lstrw As Long Dim c As Range Dim ws As Worksheet Dim newsht As Worksheet Set ws = Worksheets("RVP") With ws lstrw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each c In .Range("AA1:AA" & lstrw) c.Copy .Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Worksheets.Add(after:=Sheets(Sheets.Count)) .Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit newsht.Columns("K:K").ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & c Next End With End Sub -- Gary "ryguy7272" wrote in message ... This is kind of difficult to explain, but here goes. Below I have a loop (code is below) that filters items in AA1:AA11 and copies pastes the results into A3:A6, and loops through all items in the list of names in AA and it even assigns the person's name to the Worksheet tab. It is actually very nice, and I tip my hat to Joel for putting this together for me!! Now, the requirements have changed a bit and I'd like to know if the following is possible. Is there a way to modify this macro to apply the AutoFilter to ColumnAA, copy/paste the name into Range A3:A6 (this will always be the same), then turn off the filter, move to ColumnAB, apply the filter, and filter names in AB (these are the same names), and then copy the resulting items in AB:AC, and paste these in A11:B15 (copied down five times), then skip a row, and then go back to AB and get the next names in AB and the adjacent AC, and copy/paste these into let's say A17:B17 (remember A11:B11, then copied down 5 then skip a row). After Excel gets to the end of the list in AB (which is variable and always of different length) I would need it to turn off the filter in AB:AC and go to AD:AF and again apply the filter and copy/paste names in AD:AF and do the same as above, starting in the first unused cell in Column A (at this point I don't know how far down I will be in ColumnA). I know this is asking a heck of a lot. The looping stuff has always been tricky for me. I've been working on this since early this morning. I've tried many things in Access and Excel too (of course). Although it is very complex, I think this is the best, and most feasible, solution. However, if someone has a better solution I am open to suggestions!! Anyway, if someone here understands my need, PLEASE post back with what you think would be a solution, and I'll try to make some modifications if it doesn't work, and together, hopefully, we can get this thing working. Sub SheetsRVP() With Sheets("RVP") lstRw = .Cells(Rows.Count, "AA").End(xlUp).Row For Each C In .Range("AA1:AA11") C.Copy .Range("A3:A6").PasteSpecial Paste:=xlPasteValues Set newsht = Sheets.Add(after:=Sheets(Sheets.Count)) .Range("A1:O17").Copy _ Destination:=newsht.Range("A1") newsht.Cells.Columns.AutoFit Columns("K:K").Select Selection.ColumnWidth = 20 Range("A1").Select newsht.Name = "RVP - " & C Next C End With End Sub Regards, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
AutoFilter, Looping through the Rows? (Newbie) | Excel Programming |