tigerax
05-10-2013, 06:27 AM
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:
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: