Formula result won't copy

[Alec W]

Hi,
I have a data entry sheet in which one column holds an index match formula, the rest is just entered data.
I have a macro that copies any rows with data in column H and pastes them into another sheet. Everything works well except that the formula result does not get copied to the second sheet.
My code goes like this:

Sub SmartCopy()

Application.ScreenUpdating = False

    Dim y As Integer
    y = 1
For x = 2 To 60
    If Sheets("Entry").Cells(x, 8) <> "" Then
        Do While Sheets("Data").Cells(y, 8) <> ""
            y = y + 1
        Loop
        Sheets("Entry").Rows(x).Copy Destination:=Sheets("Data").Rows(y)
        y = 1
    End If

Next x
End Sub

macro carries on to some formatting stuff.

Any ideas?

 

[GraH - Guido]

Hi,
In here...

Do While Sheets("Data").Cells(y, 8) <> ""
            y = y + 1
       Loop

not much is going on: you are just adding 1 to the value Y. It just keeps counting on.
Perhaps you need to move this part

Sheets("Entry").Rows(x).Copy Destination:=Sheets("Data").Rows(y)

inside the previous loop, before this line

y=y+1

It could help if you would upload a file with sample data and the macro.

 

[p45cal]

Grah - Guido, I don't see anything wrong with that bit of code (so far, at least! (I haven't tried it.)); it finds the first empty cell in column H of the Destination sheet (Data). y holds the row number.

Alec W, your method of copying copies everything, formulae too, and that's perhaps not what you want (depending on the complexity of the formulae, the references within those formulae might end up referring to the wrong sheet). Just what aspects of the row you're copying from do you want to copy to the Data sheet? This will decide how you copy. If it's purely values, and you're not worried about formatting at all it could be as simple as:
Sheets("Data").Rows(y).value = Sheets("Entry").Rows(x).value

If more wants to be copied over, then the variant(s) of .PasteSpecial might be the easiest way to do it.

A file and an indication of exactly what aspects of the range you want to copy over would help us in that respect.

 

[GraH - Guido]

Hi p45cal, without seeing the data it is sometimes hard to guess what the code is meant to do. So a file would help, yes.
Seeing your reply, which makes more sense, I misunderstood the <> " " part. So thx for noticing and pointing out.