VBA Autofilter Arrays

N

NPell

Hello,

I'm trying to work if something is possible, and if so - how to do it?

At the moment i have a macro that uses autofilters and distributes to
worksheeets.

EG.
AutoFilter Field 1 = "A"
AutoFilter Field 2 = "1"
Copy and Paste to "Sheet A1"

AutoFilter Field 1 = "A"
AutoFilter Field 2 = "2"
Copy and Paste to "Sheet A2"

AutoFilter Field 1 = "B"
AutoFilter Field 2 = "1"
Copy and Paste to "Sheet B1"

AutoFilter Field 1 = "B"
AutoFilter Field 2 = "2"
Copy and Paste to "Sheet B2"

-----------

Can i do it like;
For each autofilter Field 1 in array (A,B)
Then Autofilter Field 2 in array (1,2)
Copy and paste to array (A1, A2, B1, B2)

If you get what i mean?
Thanks if you can help.
 
B

Bernie Deitrick

If the sheets don't exist yet, then use:

Sub TryNow1()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet

myF1 = Array("A", "B")
myF2 = Array(1, 2)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
For Each F2 In myF2
myR.AutoFilter Field:=1, Criteria1:=F1
myR.AutoFilter Field:=2, Criteria1:=F2
Set myS = Worksheets.Add
myS.Name = "Sheet " & F1 & F2
myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
myR.Parent.ShowAllData
Next F2
Next F1

End Sub

IF the sheets already exist, do you want the data to be over-written, or appended? Post back....

HTH,
Bernie
MS Excel MVP
 
N

NPell

If  the sheets don't exist yet, then use:

Sub TryNow1()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet

myF1 = Array("A", "B")
myF2 = Array(1, 2)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
   For Each F2 In myF2
      myR.AutoFilter Field:=1, Criteria1:=F1
      myR.AutoFilter Field:=2, Criteria1:=F2
      Set myS = Worksheets.Add
      myS.Name = "Sheet " & F1 & F2
      myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
      myR.Parent.ShowAllData
   Next F2
Next F1

End Sub

IF the sheets already exist, do you want the data to be over-written, or appended?  Post back....

HTH,
Bernie
MS Excel MVP













- Show quoted text -
Click to expand...

The sheets already exist, but there is no existing data. Is a souce
sheet, that is split out to be worked on each day.
Thanks Bernie
 
B

Bernie Deitrick

This should copy the data into an existing, other-wise blank sheet: named like "Sheet A1"

Sub TryNow2()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet

myF1 = Array("A", "B")
myF2 = Array(1, 2)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
For Each F2 In myF2
myR.AutoFilter Field:=1, Criteria1:=F1
myR.AutoFilter Field:=2, Criteria1:=F2
Set myS = Worksheets("Sheet " & F1 & F2)
myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
myR.Parent.ShowAllData
Next F2
Next F1

End Sub

HTH,
Bernie
MS Excel MVP


If the sheets don't exist yet, then use:

Sub TryNow1()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet

myF1 = Array("A", "B")
myF2 = Array(1, 2)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
For Each F2 In myF2
myR.AutoFilter Field:=1, Criteria1:=F1
myR.AutoFilter Field:=2, Criteria1:=F2
Set myS = Worksheets.Add
myS.Name = "Sheet " & F1 & F2
myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
myR.Parent.ShowAllData
Next F2
Next F1

End Sub

IF the sheets already exist, do you want the data to be over-written, or appended? Post back....

HTH,
Bernie
MS Excel MVP













- Show quoted text -
Click to expand...

The sheets already exist, but there is no existing data. Is a souce
sheet, that is split out to be worked on each day.
Thanks Bernie
 
N

NPell

This should copy the data into an existing, other-wise blank sheet: namedlike "Sheet A1"

Sub TryNow2()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet

myF1 = Array("A", "B")
myF2 = Array(1, 2)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
   For Each F2 In myF2
      myR.AutoFilter Field:=1, Criteria1:=F1
      myR.AutoFilter Field:=2, Criteria1:=F2
      Set myS = Worksheets("Sheet " & F1 & F2)
      myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
      myR.Parent.ShowAllData
   Next F2
Next F1

End Sub

HTH,
Bernie
MS Excel MVP













The sheets already exist, but there is no existing data. Is a souce
sheet, that is split out to be worked on each day.
Thanks Bernie- Hide quoted text -

- Show quoted text -
Click to expand...

I dont neccissarily want the sheet name to be the criteria (i should
have explained that better, sorry) - useful tip though!
Can i just set the sheets out in the array then??
 
B

Bernie Deitrick

Sub TryNow3()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet
Dim myF3 As Variant
Dim I As Integer


myF1 = Array("A", "B")
myF2 = Array(1, 2)
myF3 = Array("Sheet A1","Sheet A2","Sheet B1","Sheet B2")
i = LBound(myF3)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
For Each F2 In myF2
myR.AutoFilter Field:=1, Criteria1:=F1
myR.AutoFilter Field:=2, Criteria1:=F2
Set myS = Worksheets(myF3(i))
i = i + 1
myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
myR.Parent.ShowAllData
Next F2
Next F1

End Sub


Note the pattern for the sheet names....

HTH,
Bernie
MS Excel MVP

This should copy the data into an existing, other-wise blank sheet: named
like "Sheet A1"

Sub TryNow2()
Dim myF1 As Variant
Dim F1 As Variant
Dim myF2 As Variant
Dim F2 As Variant
Dim myS As Worksheet

myF1 = Array("A", "B")
myF2 = Array(1, 2)

Dim myR As Range
Set myR = Range("A2").CurrentRegion

For Each F1 In myF1
For Each F2 In myF2
myR.AutoFilter Field:=1, Criteria1:=F1
myR.AutoFilter Field:=2, Criteria1:=F2
Set myS = Worksheets("Sheet " & F1 & F2)
myR.SpecialCells(xlCellTypeVisible).Copy myS.Range("A1")
myR.Parent.ShowAllData
Next F2
Next F1

End Sub

HTH,
Bernie
MS Excel MVP













The sheets already exist, but there is no existing data. Is a souce
sheet, that is split out to be worked on each day.
Thanks Bernie- Hide quoted text -

- Show quoted text -
Click to expand...

I dont neccissarily want the sheet name to be the criteria (i should
have explained that better, sorry) - useful tip though!
Can i just set the sheets out in the array then??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there a better way to do this? 6
Excel Macro to use cells as a autofilter 0
How to make list of Items displayed in AutoFilter? Any Function? 8
Autofilter using cell reference 2
Excel 2007 Autofilter vs Excel 2003 2
Macro turn off AutoFilter 2
Autofilter problem 7
Help me4 2

Top