Difficult Looping Macro…with AutoFilter

[ryguy7272]

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---

 

[Gary Keramidas]

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

 

[ryguy7272]

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---

 

[Gary Keramidas]

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.