Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

How do we know if a name is new?

Mike

"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

Thanks for your replies. Sorry for not being clear earlier.
Need code to check for a new name in column F.
I will test John's code and let you know.
Thanks,

Manir

"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

That's not clearer. Column F is a list of names when any code runs so what is
it compared with?

"manfareed" wrote:

Thanks for your replies. Sorry for not being clear earlier.
Need code to check for a new name in column F.
I will test John's code and let you know.
Thanks,

Manir

"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

Hi John,

I am having problems with this part of your code ... how do I spli it ?

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Column "F" contains the REgion Manager name.
Once sort is complete the rows with same region manager would need to be
copied to a new sheet. I hope thsi is clear.

Thanks,

Manir


"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

It is a spreadsheet from someone else which I am trying to make sense of and
in effect tidy up.

It contains individual employee details in column"A" and their manager
Names in column "F". All this data is contained in 1 sheet. I need to create
new sheets for each of the managers. Each sheet would be named after the
manager and show only his employees.

Thanks

"Mike H" wrote:

That's not clearer. Column F is a list of names when any code runs so what is
it compared with?

"manfareed" wrote:

Thanks for your replies. Sorry for not being clear earlier.
Need code to check for a new name in column F.
I will test John's code and let you know.
Thanks,

Manir

"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

Guessed right - miles off!
I'm about to get a tooth drilled but from what you are describing I think
this link will assist you. http://www.contextures.com/excelfiles.html
There is an example workbook you can download (FL0013) which you should be
able to adjust to your need.
--
jb


"manfareed" wrote:

Hi John,

I am having problems with this part of your code ... how do I spli it ?

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Column "F" contains the REgion Manager name.
Once sort is complete the rows with same region manager would need to be
copied to a new sheet. I hope thsi is clear.

Thanks,

Manir


"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

ok thx

"john" wrote:

Guessed right - miles off!
I'm about to get a tooth drilled but from what you are describing I think
this link will assist you. http://www.contextures.com/excelfiles.html
There is an example workbook you can download (FL0013) which you should be
able to adjust to your need.
--
jb


"manfareed" wrote:

Hi John,

I am having problems with this part of your code ... how do I spli it ?

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Column "F" contains the REgion Manager name.
Once sort is complete the rows with same region manager would need to be
copied to a new sheet. I hope thsi is clear.

Thanks,

Manir


"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

John,
Hope your feeling ok ...

How do I change this code to filter from Column "F" on cell "F26".

Option Explicit

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")

'extract a list of Sales Reps
ws1.Columns("C:C").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("C1").Value

For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function

Many Thanks

"john" wrote:

Guessed right - miles off!
I'm about to get a tooth drilled but from what you are describing I think
this link will assist you. http://www.contextures.com/excelfiles.html
There is an example workbook you can download (FL0013) which you should be
able to adjust to your need.
--
jb


"manfareed" wrote:

Hi John,

I am having problems with this part of your code ... how do I spli it ?

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Column "F" contains the REgion Manager name.
Once sort is complete the rows with same region manager would need to be
copied to a new sheet. I hope thsi is clear.

Thanks,

Manir


"john" wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks

This is a great script, but for my problem, i have a massive DB of clients. I want to strip out all the rows for each suburb (contained in column C) and place them into a new sheet called the name of that suburb.
The above script creates the suburb sheets, but only places the very first row of each suburb into each sheet. I would like it to place all the rows with the same suburb into that sheet.

Thanks for your time in advance.



On Friday, November 07, 2008 8:09 AM manfaree wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks


On Friday, November 07, 2008 8:15 AM Mike wrote:

How do we know if a name is new?

Mike

"manfareed" wrote:


On Friday, November 07, 2008 9:11 AM joh wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:


On Friday, November 07, 2008 10:07 AM manfaree wrote:

Thanks for your replies. Sorry for not being clear earlier.
Need code to check for a new name in column F.
I will test John's code and let you know.
Thanks,

Manir

"john" wrote:


On Friday, November 07, 2008 10:24 AM Mike wrote:

That's not clearer. Column F is a list of names when any code runs so what is
it compared with?

"manfareed" wrote:


On Friday, November 07, 2008 10:37 AM manfaree wrote:

Hi John,

I am having problems with this part of your code ... how do I spli it ?

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Column "F" contains the REgion Manager name.
Once sort is complete the rows with same region manager would need to be
copied to a new sheet. I hope thsi is clear.

Thanks,

Manir


"john" wrote:


On Friday, November 07, 2008 10:44 AM manfaree wrote:

It is a spreadsheet from someone else which I am trying to make sense of and
in effect tidy up.

It contains individual employee details in column"A" and their manager
Names in column "F". All this data is contained in 1 sheet. I need to create
new sheets for each of the managers. Each sheet would be named after the
manager and show only his employees.

Thanks

"Mike H" wrote:


On Friday, November 07, 2008 11:03 AM joh wrote:

Guessed right - miles off!
I'm about to get a tooth drilled but from what you are describing I think
this link will assist you. http://www.contextures.com/excelfiles.html
There is an example workbook you can download (FL0013) which you should be
able to adjust to your need.
--
jb


"manfareed" wrote:


On Friday, November 07, 2008 11:09 AM manfaree wrote:

ok thx

"john" wrote:


On Friday, November 07, 2008 12:26 PM manfaree wrote:

John,
Hope your feeling ok ...

How do I change this code to filter from Column "F" on cell "F26".

Option Explicit

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")

'extract a list of Sales Reps
ws1.Columns("C:C").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("C1").Value

For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function

Many Thanks

"john" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Assemblies in Folder Debug Build Checker
http://www.eggheadcafe.com/tutorials...d-checker.aspx

This is a great script, but for my problem, i have a massive DB of clients. I want to strip out all the rows for each suburb (contained in column C) and place them into a new sheet called the name of that suburb.
The above script creates the suburb sheets, but only places the very first row of each suburb into each sheet. I would like it to place all the rows with the same suburb into that sheet.

Thanks for your time in advance.

Dave.

On Friday, November 07, 2008 8:09 AM manfaree wrote:

Hi ,

I need to sort data by column F [FROM F27]. Then create new sheets based on
each new name in column F.

Thanks


On Friday, November 07, 2008 8:15 AM Mike wrote:

How do we know if a name is new?

Mike

"manfareed" wrote:


On Friday, November 07, 2008 9:11 AM joh wrote:

not sure if I've fully understood what you are trying to do an mikes comment
aludes to lack of clear info.

As stab in the dark something along the lines of following may do what you
want? but there again, could be miles off!

Sub NewSheet()
Dim sh As Worksheet
Dim NewName As String


Application.ScreenUpdating = False

RN = 27
With ThisWorkbook.Worksheets("Sheet1") '<< change as required

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Do

NewName = .Range("F" & RN).Value

On Error Resume Next

Set sh = Worksheets(NewName)

On Error GoTo 0

If sh Is Nothing Then
Worksheets.Add.Name = (NewName)
End If

RN = RN + 1

Loop Until .Range("F" & RN).Value = ""

End With

Application.ScreenUpdating = True

End Sub

--
jb


"manfareed" wrote:


On Friday, November 07, 2008 10:07 AM manfaree wrote:

Thanks for your replies. Sorry for not being clear earlier.
Need code to check for a new name in column F.
I will test John's code and let you know.
Thanks,

Manir

"john" wrote:


On Friday, November 07, 2008 10:24 AM Mike wrote:

That's not clearer. Column F is a list of names when any code runs so what is
it compared with?

"manfareed" wrote:


On Friday, November 07, 2008 10:37 AM manfaree wrote:

Hi John,

I am having problems with this part of your code ... how do I spli it ?

LastRow = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("F27:F" & LastRow).Sort Key1:=.Range("F27"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Column "F" contains the REgion Manager name.
Once sort is complete the rows with same region manager would need to be
copied to a new sheet. I hope thsi is clear.

Thanks,

Manir


"john" wrote:


On Friday, November 07, 2008 10:44 AM manfaree wrote:

It is a spreadsheet from someone else which I am trying to make sense of and
in effect tidy up.

It contains individual employee details in column"A" and their manager
Names in column "F". All this data is contained in 1 sheet. I need to create
new sheets for each of the managers. Each sheet would be named after the
manager and show only his employees.

Thanks

"Mike H" wrote:


On Friday, November 07, 2008 11:03 AM joh wrote:

Guessed right - miles off!
I'm about to get a tooth drilled but from what you are describing I think
this link will assist you. http://www.contextures.com/excelfiles.html
There is an example workbook you can download (FL0013) which you should be
able to adjust to your need.
--
jb


"manfareed" wrote:


On Friday, November 07, 2008 11:09 AM manfaree wrote:

ok thx

"john" wrote:


On Friday, November 07, 2008 12:26 PM manfaree wrote:

John,
Hope your feeling ok ...

How do I change this code to filter from Column "F" on cell "F26".

Option Explicit

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")

'extract a list of Sales Reps
ws1.Columns("C:C").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("C1").Value

For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function

Many Thanks

"john" wrote:


On Friday, August 13, 2010 8:48 AM David Auld wrote:

This is a great script, but for my problem, i have a massive DB of clients. I want to strip out all the rows for each suburb (contained in column C) and place them into a new sheet called the name of that suburb.

The above script creates the suburb sheets, but only places the very first row of each suburb into each sheet. I would like it to place all the rows with the same suburb into that sheet.



Thanks for your time in advance.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Composite UI Pattern and RAD Development for Data Entry Applications, Part 1
http://www.eggheadcafe.com/tutorials...ns-part-1.aspx