Copy row from one sheet (without a specific text) to another

[eaje92]

Hi all, I current have 2 sheets (one with data and one without). I would like to copy the entire row of those cells with number in the first sheet and paste it into another. It seems i have to search down the column and see ISnumeric()? if so then row().copy and paste?

Sheet1

Number	Year
1	1992
NA	NA
NA	NA
4	1880
NA	NA

<tbody>
</tbody>

Sheet2

Number	Year
1	1992
4	1880

<tbody>
</tbody>

Thank you!

 

[eaje92]

Hello Eaje92,

Did you try the code in post #3 ?

Cheerio,
vcoolio.

Hi Vcoolio, unfortunately the code does nothing even after changing the variables to my data set. Thank you though!

 

[kvsrinivasamurthy]

Try this code
DATA Range in Sheet1 is A2:B6
Data starts in Sheet2 from 3rd Row.

Sub CopyData()Dim LR As Long, X As Long, TA As Long
Dim Sh1 As Worksheet, Sh2 As Worksheet


Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")


LR = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Sh1.Range("A1:B1").Copy Sh2.Range("A2")
X = 3


For TA = 2 To LR


If IsNumeric(Sh1.Range("A" & TA)) Then
Sh1.Range("A" & TA & ":B" & TA).Copy Sh2.Range("A" & X)
X = X + 1
End If


Next TA


End Sub

 

[vcoolio]

Hello Eaje92,

Here is a little sample which shows the code working as it should:-

http://ge.tt/7sgwz2p2

Just as Trebor had assumed, I had assumed that you were using Columns A and B. If your opening post is not truly indicative of the set out of your workbook, then please upload a sample (an exact replica of your workbook but using dummy data) to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here.

Cheerio,
vcoolio.