Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > VBA Autofilter Arrays
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 76
Default 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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default 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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 76
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 76
Default 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??
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default 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??

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
2007 Autofilter worse than 2003 Autofilter jsky Excel Discussion (Misc queries) 9 October 31st 07 12:14 AM
Use of arrays DKS Excel Worksheet Functions 1 November 30th 06 08:38 PM
Use of arrays Dave F Excel Worksheet Functions 0 November 30th 06 04:26 PM
Use of arrays DKS Excel Worksheet Functions 0 November 30th 06 04:11 PM
How to Sort within AutoFilter with Protection on (and AutoFilter . giblon Excel Discussion (Misc queries) 1 February 16th 06 12:23 PM


All times are GMT +1. The time now is 06:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement