Urgent Formula Help
 

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information onto
a new worksheet.

Many thanks!

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information
onto
a new worksheet.

Many thanks!

Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I need to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information
onto
a new worksheet.

Many thanks!

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...

Can anyone help with this problem, I would like a formula to read a
column
and any entries over a certain number e.g. 10 it copies the
information
onto
a new worksheet.

Many thanks!

Peter

What is "it didn't work"? Nothing happened? Error message? Wrong rows
copied?

Works fine for me. Finds all rows in column A with data over 10 and copies
those rows to a new worksheet named CopyData.

The code would be placed in a General Module, not worksheet or ThisWorkbook.


Gord Dibben Excel MVP

On Mon, 10 Jan 2005 08:25:06 -0800, Peter Curtis
wrote:

Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I need to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information
onto
a new worksheet.

Many thanks!

If I were doing this manually, I'd apply Data|filter|autofilter to column A.

Then I'd filter by:
custom|greater than or equal to 10.

Then I'd copy those visible rows and paste them where ever I wanted.

(If I needed a macro, I'd record one when I did it manually.)

Peter Curtis wrote:

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information onto
a new worksheet.

Many thanks!

--

Dave Peterson

Hi Bob,

I haven't used VBA much, the error I get is a compile error, expected end sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...

Can anyone help with this problem, I would like a formula to read a
column
and any entries over a certain number e.g. 10 it copies the
information
onto
a new worksheet.

Many thanks!

Hi Gord,

I haven't used VBA much, the error I get is a compile error, expected end sub

Any ideas?

Many thanks for your help

"Gord Dibben" wrote:

Peter

What is "it didn't work"? Nothing happened? Error message? Wrong rows
copied?

Works fine for me. Finds all rows in column A with data over 10 and copies
those rows to a new worksheet named CopyData.

The code would be placed in a General Module, not worksheet or ThisWorkbook.


Gord Dibben Excel MVP

On Mon, 10 Jan 2005 08:25:06 -0800, Peter Curtis
wrote:

Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I need to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information
onto
a new worksheet.

Many thanks!

On what line of code do you get the error?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Peter Curtis" wrote in
message
...
Hi Gord,

I haven't used VBA much, the error I get is a compile error,
expected end sub

Any ideas?

Many thanks for your help

"Gord Dibben" wrote:

Peter

What is "it didn't work"? Nothing happened? Error message?
Wrong rows
copied?

Works fine for me. Finds all rows in column A with data over
10 and copies
those rows to a new worksheet named CopyData.

The code would be placed in a General Module, not worksheet or
ThisWorkbook.


Gord Dibben Excel MVP

On Mon, 10 Jan 2005 08:25:06 -0800, Peter Curtis
wrote:

Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would
assume I need to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _

Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis"
wrote in message
...

Can anyone help with this problem, I would like a formula
to read a column
and any entries over a certain number e.g. 10 it copies
the information
onto
a new worksheet.

Many thanks!

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the last line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error, expected end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...

Can anyone help with this problem, I would like a formula to read
a
column
and any entries over a certain number e.g. 10 it copies the
information
onto
a new worksheet.

Many thanks!

Thanks Dave

"Dave Peterson" wrote:

If I were doing this manually, I'd apply Data|filter|autofilter to column A.

Then I'd filter by:
custom|greater than or equal to 10.

Then I'd copy those visible rows and paste them where ever I wanted.

(If I needed a macro, I'd record one when I did it manually.)

Peter Curtis wrote:

Can anyone help with this problem, I would like a formula to read a column
and any entries over a certain number e.g. 10 it copies the information onto
a new worksheet.

Many thanks!

--

Dave Peterson

Hi Chip,

After end sub

Regards

"Chip Pearson" wrote:

On what line of code do you get the error?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Peter Curtis" wrote in
message
...
Hi Gord,

I haven't used VBA much, the error I get is a compile error,
expected end sub

Any ideas?

Many thanks for your help

"Gord Dibben" wrote:

Peter

What is "it didn't work"? Nothing happened? Error message?
Wrong rows
copied?

Works fine for me. Finds all rows in column A with data over
10 and copies
those rows to a new worksheet named CopyData.

The code would be placed in a General Module, not worksheet or
ThisWorkbook.


Gord Dibben Excel MVP

On Mon, 10 Jan 2005 08:25:06 -0800, Peter Curtis
wrote:

Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would
assume I need to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _

Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis"
wrote in message
...

Can anyone help with this problem, I would like a formula
to read a column
and any entries over a certain number e.g. 10 it copies
the information
onto
a new worksheet.

Many thanks!

Bob,

Good spot! I missed the end sub!

It now works but copies all the entires not just the ones over 10.

Any ideas?

Thanks again.

Peter

"Bob Phillips" wrote:

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the last line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error, expected end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j, "A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...

Can anyone help with this problem, I would like a formula to read
a
column
and any entries over a certain number e.g. 10 it copies the
information
onto
a new worksheet.

Many thanks!

Peter,

There is a test for greater than 10. AS I said, insufficient details mean
that I had to guess. I am testing the value in column A for 10. Is that
the correct column?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

Good spot! I missed the end sub!

It now works but copies all the entires not just the ones over 10.

Any ideas?

Thanks again.

Peter

"Bob Phillips" wrote:

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the last
line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error, expected
end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume
I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j,
"A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...

Can anyone help with this problem, I would like a formula to
read
a
column
and any entries over a certain number e.g. 10 it copies the
information
onto
a new worksheet.

Many thanks!

Bob,

It works, the only minor problem is that some of the entries 10 come up
with a REF error?

Any ideas?

Thank you for your continued assistance

"Bob Phillips" wrote:

Peter,

There is a test for greater than 10. AS I said, insufficient details mean
that I had to guess. I am testing the value in column A for 10. Is that
the correct column?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

Good spot! I missed the end sub!

It now works but copies all the entires not just the ones over 10.

Any ideas?

Thanks again.

Peter

"Bob Phillips" wrote:

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the last
line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error, expected
end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would assume
I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j,
"A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...

Can anyone help with this problem, I would like a formula to
read
a
column
and any entries over a certain number e.g. 10 it copies the
information
onto
a new worksheet.

Many thanks!

Presumably that is because there are some formulae, and copying the row
breaks the formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

It works, the only minor problem is that some of the entries 10 come up
with a REF error?

Any ideas?

Thank you for your continued assistance

"Bob Phillips" wrote:

Peter,

There is a test for greater than 10. AS I said, insufficient details
mean
that I had to guess. I am testing the value in column A for 10. Is
that
the correct column?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

Good spot! I missed the end sub!

It now works but copies all the entires not just the ones over 10.

Any ideas?

Thanks again.

Peter

"Bob Phillips" wrote:

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the
last
line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error,
expected
end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would
assume
I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j,
"A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote
in
message
...

Can anyone help with this problem, I would like a formula
to
read
a
column
and any entries over a certain number e.g. 10 it copies
the
information
onto
a new worksheet.

Many thanks!

is there any code that can copy all of the formulaes?

Thanks

"Bob Phillips" wrote:

Presumably that is because there are some formulae, and copying the row
breaks the formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

It works, the only minor problem is that some of the entries 10 come up
with a REF error?

Any ideas?

Thank you for your continued assistance

"Bob Phillips" wrote:

Peter,

There is a test for greater than 10. AS I said, insufficient details
mean
that I had to guess. I am testing the value in column A for 10. Is
that
the correct column?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

Good spot! I missed the end sub!

It now works but copies all the entires not just the ones over 10.

Any ideas?

Thanks again.

Peter

"Bob Phillips" wrote:

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the
last
line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error,
expected
end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I would
assume
I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _
Destination:=Worksheets("CopyData").Cells(j,
"A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote
in
message
...

Can anyone help with this problem, I would like a formula
to
read
a
column
and any entries over a certain number e.g. 10 it copies
the
information
onto
a new worksheet.

Many thanks!

Peter,
That code copies the formulae as well. I was suggesting that a copied
formula broke, that is it is maybe referring to something that doesn't work
on the copied sheet.

Take a look at one of the #REFs and see why it doesn't work.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
is there any code that can copy all of the formulaes?

Thanks

"Bob Phillips" wrote:

Presumably that is because there are some formulae, and copying the row
breaks the formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in message
...
Bob,

It works, the only minor problem is that some of the entries 10 come
up
with a REF error?

Any ideas?

Thank you for your continued assistance

"Bob Phillips" wrote:

Peter,

There is a test for greater than 10. AS I said, insufficient details
mean
that I had to guess. I am testing the value in column A for 10. Is
that
the correct column?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Bob,

Good spot! I missed the end sub!

It now works but copies all the entires not just the ones over 10.

Any ideas?

Thanks again.

Peter

"Bob Phillips" wrote:

Peter,

It shouldn't do, there is an End Sub in the code.

Did you copy all of the code into a standard code module? Is the
last
line

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote in
message
...
Hi Bob,

I haven't used VBA much, the error I get is a compile error,
expected
end
sub

Any ideas?

Many thanks for your help

"Bob Phillips" wrote:

Peter,

There were not enough details really, so I made some
guesses.

Tell me how it doesn't work, what it should do.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Curtis" wrote
in
message
...
Hi Bob

Thanks for the reply. Unfortunately it didn't work, I
would
assume
I
need
to
simplify it some how?

Regards,

Peter

"Bob Phillips" wrote:

This might need some clarification

Sub CopyData()
Dim ws As Worksheet
Dim cLastRow As Long
Dim i As Long
Dim j As Long

Set ws = ActiveSheet
cLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Worksheets.Add.Name = "CopyData"
Worksheets("CopyData").ClearContents
On Error GoTo 0
j = 1
For i = 1 To cLastRow
If ws.Cells(i, "A").Value 10 Then
ws.Cells(i, "A").EntireRow.Copy _

Destination:=Worksheets("CopyData").Cells(j,
"A")
j = j + 1
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing
direct)


"Peter Curtis"
wrote
in
message

...

Can anyone help with this problem, I would like a
formula
to
read
a
column
and any entries over a certain number e.g. 10 it
copies
the
information
onto
a new worksheet.

Many thanks!