Looping filter criteria

Hi all,

What's the code to loop through a Autofilter list.

e.g John, Jack , Tim

Selection.AutoFilter Field:=1, Criteria1:="John"

VBA Noob

Hi VBA Noob

You can create a list with advancedfilter an loop through this list
See how I use it here
http://www.rondebruin.nl/copy5.htm

Look at this one for example
http://www.rondebruin.nl/copy5.htm#all


--
Regards Ron de Bruin
http://www.rondebruin.nl



"VBA Noob" <VBA.Noob.2cecw6_1155335415.8229@excelforum-nospam.com> wrote in message
news:VBA.Noob.2cecw6_1155335415.8229@excelforum-nospam.com...
>
> Hi all,
>
> What's the code to loop through a Autofilter list.
>
> e.g John, Jack , Tim
>
> Selection.AutoFilter Field:=1, Criteria1:="John"
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=570946
>

Thanks Ron.....again

Happy Birthday by the way.



VBA Noob

Hi Ron,

Can you paste both Formulas and values with advanced filter

VBA Noob

No, not possible

--
Regards Ron de Bruin
http://www.rondebruin.nl



"VBA Noob" <VBA.Noob.2cfjhf_1155390606.97@excelforum-nospam.com> wrote in message
news:VBA.Noob.2cfjhf_1155390606.97@excelforum-nospam.com...
>
> Hi Ron,
>
> Can you paste both Formulas and values with advanced filter
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=570946
>

Would I be better with a Filter then

How would I code say

Sheet3 A2 to A29 as a array of names. e.g John Davis and then loop through all names to use as "Criteria1:" for filter.

VBA Noob

Hi VBA Noob

Also not working with AutoFilter
You must loop through the data and copy each row that have your criteria


--
Regards Ron de Bruin
http://www.rondebruin.nl



"VBA Noob" <VBA.Noob.2cfu4s_1155404411.4808@excelforum-nospam.com> wrote in message
news:VBA.Noob.2cfu4s_1155404411.4808@excelforum-nospam.com...
>
> Would I be better with a Filter then
>
> How would I code say
>
> Sheet3 A2 to A29 as a array of names. e.g John Davis and then loop
> through all names to use as "Criteria1:" for filter.
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=570946
>

Ron,

Instead of looping....wouldn't know where to start I was thinking I could insert a column at T to break the current region. Columns T onwards has the formulas

Then use a template with my formulas to paste into.

My question then is how would I adpat your code to copy the template then paste into the copy e.g Template (2)

Thanks for staying with this one for me.

VBA Noob

Post a example for you today

--
Regards Ron de Bruin
http://www.rondebruin.nl



"VBA Noob" <VBA.Noob.2ch39p_1155462904.9948@excelforum-nospam.com> wrote in message
news:VBA.Noob.2ch39p_1155462904.9948@excelforum-nospam.com...
>
> Ron,
>
> Instead of looping....wouldn't know where to start I was thinking I
> could insert a column at T to break the current region. Columns T
> onwards has the formulas
>
> Then use a template with my formulas to paste into.
>
> My question then is how would I adpat your code to copy the template
> then paste into the copy e.g Template (2)
>
> Thanks for staying with this one for me.
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=570946
>

Thanks Ron.




VBA Noob

Ok

Insert a new sheet in your workbook with In T1:W1 the headers
In T2:W2 your formulas and name the sheet "template

Now try this one that autofill the formulas in row 2 to the last data in column A


Sub Copy_With_AdvancedFilter_To_Worksheets()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim lastrow As Long

Set ws1 = Sheets("Sheet1") '<<< Change
'Tip : Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ws1
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
'This example filter on the first column in the range (change this if needed)
'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the columns)

Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Sheets("template").Copy after:=ws1
Set WSNew = ActiveSheet
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WSNew.Range("A1"), _
Unique:=False

'WSNew.Columns.AutoFit

With WSNew
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("T2:W2").AutoFill Destination:=.Range("T2:W" & lastrow) _
, Type:=xlFillDefault
End With

Next
.Columns("IU:IV").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"VBA Noob" <VBA.Noob.2chlsf_1155486907.1137@excelforum-nospam.com> wrote in message
news:VBA.Noob.2chlsf_1155486907.1137@excelforum-nospam.com...
>
> Thanks Ron.
>
>
>
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=570946
>