VBA Autofilter Arrays

  • Thread starter Thread starter NPell
  • Start date Start date
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.
 
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
 
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
 
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
 
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??
 
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??
 
Back
Top