The only reason why it would stop is if the destination cell in my example
K1, is not empty. Make sure the K column is clear of any data or change it to
a column where there will never be any data.
Range("Z1".....

One more thing: I did not use the col1...Col8 reference I modified the
example so that the headers would be:
date job operator ordered start # stop # run var -+
var %




"kjp" wrote:

Thanks. I added the sub to the worksheet and got a compilation error all
highlighted in red when I ran the compiler)...

[this line is highlighted as incorrect by the compiler]
wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy,

CopyToRange:=Range("K1" _
), Unique:=True .....

seems this statement is not valid. As I know little about Macros and
subroutines, I'm not sure what I'm looking at. Do I need to change
something to make it work?


"Michael" wrote in message
...
You will not be able to find a formula, but I build a subroutine that you
can
use to get you started. it assumes 9 colums, I used your example as a
reference. The only thing I added was naming the sheet where the base data
is
as "Source".

Cheers,


Sub Macro1()
Dim iLastOp As Integer
Dim wks As Worksheet
Dim wksdest As Worksheet
Dim rngOperat As Range
Dim rngFiltOperat As Range
Dim Scell As Range
Dim Wkname As String

ThisWorkbook.Application.ScreenUpdating = False
iLastOp = Range("C65536").End(xlUp).Row
Set wks = ThisWorkbook.Worksheets("Source")
wks.Range("K:K").ClearContents
wks.Range("C1:C" & iLastOp).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("K1" _
), Unique:=True
iLastOp = wks.Range("K65536").End(xlUp).Row

Set rngOperat = Range("K2:K" & iLastOp)

For Each rngOperat In rngOperat
ThisWorkbook.Worksheets.Add
Worksheets(1).Name = rngOperat
Next rngOperat
Set rngFiltOperat = wks.Range("K2:K" & iLastOp)
wks.Select

wks.Columns("C:C").AutoFilter
For Each rngFiltOperat In rngFiltOperat
wks.Select
wks.Range("C65536").End(xlUp).Select

With Selection
.AutoFilter Field:=1, Criteria1:=rngFiltOperat
End With
Wkname = rngFiltOperat
Set wksdest = ThisWorkbook.Worksheets(Wkname)
Cells.Copy
wksdest.Paste
wksdest.Range("K:K").ClearContents
Next rngFiltOperat
wks.Select
wks.Range("C:C").AutoFilter
wks.Range("K:K").ClearContents
ThisWorkbook.Application.ScreenUpdating = True
End Sub


"kjp55" wrote:

I have a basic spreadsheet and need to import data from that sheet into a
new
sheet, based on criteria from one colum in the original sheet. Can't
figure
out a formula for this.

Sheet One (Printing press waste calculator)

col1 col2 col3 col4 col5 col6
col7 col8 col9
date job operator ordered start # stop # run
var -+ var %

6-6-06 test Dan 5,000 415250 420450 5200
200 4%
6-6-06 test Sam 5,000 415250 420450 5200
200 4%
6-6-06 test Ed 5,000 415250 420450 5200
200 4%
6-6-06 test Dan 5,000 415250 420450 5200
200 4%
6-6-06 test Ed 5,000 415250 420450 5200
200 4%
6-6-06 test Sam 5,000 415250 420450 5200
200 4%

I need a new sheet for each operator, importing all rows of data, but
only
by the operator's name. So, the resulting worksheet will show everything
(like above) but will only show data for a particular operator.



I need the new sheet to import all data