Using data from a AutoFilter list 1

[rocknrisk]

Hi all,

Please can someone me with 2 problems I'm having with macros in Excel 97.

How can I input the items in the AutoFilter list into an a array so that I can manipulate the data grouped by the AutoFilter list. Basically, if I had a column with a few 1s, 5s, and 8s in it, then AutoFiltered that column, I'd get a choice of All, 1, 5, 8, Blanks, and Non Blanks in the AutoFilter list. I need a macro to be able to select "1" from the list then copy and paste the data in the column, then select "All" to reset the list and column. I then need to select "5" from the list then copy and paste the data in the column, then select "All" to reset the list and column. ... And so forth. If the column only had 1s, 5s, and 8s, etc (i.e. known figures) it would be easy but the data differs from spreadsheet to spreadsheet so I need the macro to be able to work out what numbers will be in the column and the AutoFilter them in a loop.
Secondly, I need to create new Sheets and name them, say, Day1, Day5, and Day8 (i.e. the word "Day" and the number from the AutoFilter list. I know how to create the new Sheet via macro but am having a problem naming the new Sheets as they sould be named.

I hope this all makes sense. Please can someone help?

Best regards,

Clinton Edwards


"The important thing is not to stop questioning." - Albert Einstein

 

[xlbo]

If you can sort your data by that column then do so and run something like this:

dim lRow as long, nSht as worksheet, fSht as worksheet
const mCol = 3 'assumes the filter column is C
lRow = cells(65536,3).end(xlup).row
fSht = sheets("NameOfSheetWhereFiltereableDataIs")
for each c in range("C2:C" & lrow)
 if c.value <> c.offset(-1,0).value then
  'change in value
   range(&quot;A1:Z&quot; & lRow).autofilter field:= mCol criteria1:= c.value
 set nSht = sheets.add after:=sheets(sheets.count)
 fSht.range(&quot;A1:Z&quot; & lrow).copy destination:=nSht.range(&quot;A1&quot;)
nSht.name = &quot;Day&quot; & c.value
 else
 'no change in value
 end if

Assumes the "days" are held in col 3 and data is in A:Z - change as appropriate

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rocknrisk]

Hey Geoff,

Thank you. Your idea worked. I have given you a star in appreciation. You are a star, mate.

Best regards,
Clinton

"The important thing is not to stop questioning." - Albert Einstein