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!

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,

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

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!

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!

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

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