Hi,

I tried this and I get a compile error at this line, it concerns the Offset

Sheets("Matched").Range("A65536").End(xlUp).Offset (1, 0)

Error message: "= expected"

I have no excell programming experience...

How can I change this to copy only a few columns:
if cell.value 0 then cell.EntireRow.Cut

Thanks.

Marco.


"Jef Gorbach" wrote:


"Marco" wrote in message
...
Hi,

I have a worksheet with several columns and in 1 column there is a number
from 0 to 10. I would like to copy "automatic" all rows from this
worksheet
to another worksheet but only if the number is different from 0. This is
not
a one-time operation but the numbers can dynamically change, they are
formula
based. I have not found a solution yet. Has anyone experience with this ?

Marco.

Here's one way:

'copy input data to a temp worksheet then switch to it for maniupation in
case anything goes wrong
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "temp"

'add desitnation sheets for the matching data
Sheets.Add.Name = "Matched"
sheets("Matched").Range("A1:G1").Value =
Sheets("Temp").Range("A1:G1").Value 'copy title row

'any processing you do prior to seperating the matches needs completed here

'sort matching data to destination sheets
'change column(G) to your longest data column
Sheets("temp").Activate
For Each cell In Range("G1:G" & Range("G65536").End(xlUp).Row)
if cell.value 0 then cell.EntireRow.Cut
Sheets("Matched").Range("A65536").End(xlUp).Offset (1, 0)
Next

'presuming you want to remove the temp worksheet once data is seperated,
without bothering the user
Application.DisplayAlerts = False
Sheets("temp").Delete
Application.DisplayAlerts = True

'any followup processing goes here
end sub