Urgent Formula Help

[Peter Curtis]

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!

[Rutgers_Excels]

Your question is pretty unclear. Can you give an example?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 Curtis]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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!
> >
> >
> >

[Gord Dibben]

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
<[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > 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]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > >
> > > > > 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 Curtis]

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" <[email protected]> wrote in
> message
> news:[email protected]...
> > 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
> >> <[email protected]> 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" <[email protected]>
> >> >> wrote in message
> >> >> news:[email protected]...
> >> >> >
> >> >> > 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 Curtis]

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
>

[Chip Pearson]

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" <[email protected]> wrote in
message
news:[email protected]...
> 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
>> <[email protected]> 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" <[email protected]>
>> >> wrote in message
>> >> news:[email protected]...
>> >> >
>> >> > 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 Curtis]

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
> <[email protected]> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >
> >> > 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 Curtis]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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 Curtis]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > >
> > > > > > 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
> > message
> > > > > > news:[email protected]...
> > > > > > >
> > > > > > > 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 Curtis]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > news:[email protected]...
> > > > > > > >
> > > > > > > > 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
> > message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote
in
> > > > message
> > > > > > > > news:[email protected]...
> > > > > > > > >
> > > > > > > > > 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 Curtis]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote
> in
> > > > > message
> > > > > > > > > news:[email protected]...
> > > > > > > > > >
> > > > > > > > > > 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
> > message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote
in
> > > > message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]>
wrote
> > in
> > > > > > message
> > > > > > > > > >
news:[email protected]...
> > > > > > > > > > >
> > > > > > > > > > > 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!
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[RobertR]

To Bob Philips,
I used the code provided by you and it works fine. How should I recode for the
"10" to refer to cell A1 on Sheet1. This cell contains a date with the format dd-mmm-yy.

Thank you

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