VBA Autofilter Arrays
On Jan 30, 4:28*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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
"NPell" wrote in message
...
On Jan 30, 3:03 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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
"NPell" wrote in message
....
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.- Hide quoted text -
- Show quoted text -
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 -
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??
|