Writing VBA code to loop

[tigerax]

Hi there..my first post.

I have some VBA code which uses some criteria to suck data from one sheet to another using a column reference and the name of the sheet.

for example if my sheet name was called "hello" then the VBA code would look on the other sheet and column specified and anything with the text "hello" it would copy those rows into my "hello" sheet.

What i want to do is create a loop to use the same code but my particular column has numbers from 1 to (unknown).

So I don't know how many sheets I need to create.

If I use this Dim MaxVal As Integer
MaxVal = Application.WorksheetFunction.Max(Range("P:P")) I can get the maximum value but don't know how to loop it.

I was thinking something like this..


Dim Source As Worksheet ' Sheet containing data
Dim Crit As String
Dim MaxVal As Integer
MaxVal = Application.WorksheetFunction.Max(Range("P:P"))
Dim i As Long
i = 1 To MaxVal
For Each i
'this creates the initial sheet download the data into
Sheets.Add
ActiveSheet.Name = i
Sheets(i).Select
Set Source = Worksheets("MATCH DATA")
' Sheet name is data required
Crit = i
With Source.Range("A1").CurrentRegion
' Filter for column 5 = sheet name
.AutoFilter Field:=16, Criteria1:=Crit
' Copy data to active sheet
.Copy ActiveSheet.Range("A1")
' Turn off Autofilter
.AutoFilter
End With
Next i

My syntax is definitely wrong.. I've copied some of the code.. If I remove the for i and name everything it will work fine. Just looking for some help really to correct it as I don't understand what I'm doing now :think:

[Bob Phillips]

Untested


Dim Source As Worksheet ' Sheet containing data
Dim Crit As String
Dim MaxVal As Integer
Dim i As Long
MaxVal = Application.WorksheetFunction.Max(Range("P:P"))
Set Source = Worksheets("MATCH DATA")
For i = 1 To MaxVal
'this creates the initial sheet download the data into
Sheets.Add
ActiveSheet.Name = i
' Sheet name is data required
Crit = i
With Source.Range("A1").CurrentRegion
' Filter for column 5 = sheet name
.AutoFilter Field:=16, Criteria1:=Crit
' Copy data to active sheet
.Copy ActiveSheet.Range("A1")
' Turn off Autofilter
.AutoFilter
End With
Next i

[tigerax]

Perfect.. So my mistake was not using


For i = 1 To MaxVal

Thanks so much

[tigerax]

Hmm might have jumped the gun actually..

It is creating all my sheets properly but for some reason not dragging through the correct sheet data. Only sheet with the number 1 dragged through data but for my max value which was 12.. so sheet named 1 dragged through 12 data and all the rest were blank

[tigerax]

ah I changed


crit = i[/vba] to [vba]Crit = ActiveSheet.Name

Works now