Urgent Formula Help

P

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!
 
B

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)
 
P

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
 
B

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)
 
G

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
 
D

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.)
 
P

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
 
P

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
 
B

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)
 
P

Peter Curtis

Thanks Dave

Dave Peterson said:
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.)
Click to expand...
 
P

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
 
B

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)
 
P

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
 
B

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)
 
B

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)
 
Top