ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   VBA Autofilter Arrays (https://www.excelbanter.com/excel-worksheet-functions/218468-vba-autofilter-arrays.html)

NPell
VBA Autofilter Arrays
 
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.

Bernie Deitrick
VBA Autofilter Arrays
 
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.

NPell
VBA Autofilter Arrays
 
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

Bernie Deitrick
VBA Autofilter Arrays
 
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



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

Bernie Deitrick
VBA Autofilter Arrays
 
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

"NPell" wrote in message
...
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??



All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com