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!

 

[cebalaw2]

This code may work for you, simply modify the Initialization section to match your needs:

Public Sub CopyRow()
    
    Dim wsSheet1 As Worksheet
    Dim wsSheet2 As Worksheet
    Dim intDataColumn As Integer
    Dim lngStartRow As Long
    Dim lngLastRow As Long
    Dim rngRow As Range
    Dim strData As String
    Dim lngPasteRow As Long
    
    'Initialization
    Set wsSheet1 = Sheet1
    Set wsSheet2 = Sheet2
    intDataColumn = 1
    lngStartRow = 2
    lngPasteRow = 2
    
    Application.ScreenUpdating = False
    With wsSheet1
        lngLastRow = .Cells(lngStartRow, intDataColumn).End(xlDown).Row
        
        'Loop the data rows
        For Each rngRow In .Range(.Cells(lngStartRow, intDataColumn), .Cells(lngLastRow, intDataColumn))
            strData = rngRow.Cells(, intDataColumn).Value2
            If (strData <> vbNullString) And IsNumeric(strData) Then
                
                'Copy the data when source is numeric
                .Rows(rngRow.Row).Copy
                wsSheet2.Rows(lngPasteRow).PasteSpecial xlPasteValues
                lngPasteRow = lngPasteRow + 1
            End If
        Next
    End With
    Application.ScreenUpdating = True
    
    'Destroy objects
    Set wsSheet1 = Nothing
    Set wsSheet2 = Nothing
    Set rngRow = Nothing
End Sub

 

[vcoolio]

Hello Eaje92,

You may only need one line of code to do the task for you. Try the following:-

Sub TransferData()

On Error Resume Next
Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(2, 1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)

End Sub

It assumes the numbers are in Column A.

I hope that this helps.

Cheerio,
vcoolio.

 

[Trebor76]

Here's my attempt:

Option Explicit
Sub Macro1()

    Dim lngMyRow   As Long
    Dim lngLastRow As Long
        
    lngLastRow = Sheets("Sheet1").Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 2 To lngLastRow
        If IsNumeric(Sheets("Sheet1").Range("A" & lngMyRow)) Then
            Sheets("Sheet1").Rows(lngMyRow).EntireRow.Copy _
                Destination:=Sheets("Sheet2").Range("A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1)
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[eaje92]

Hey cebalaw, Thanks for your reply. It seems im having some error on strData = rngRow.Cells(, intDataColumn).Value2

 

[eaje92]

Hey Trebor, thank you for your reply. However im getting a subscript out of range error on lngLastRow = Sheets("Sheet1").Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Trebor76]

That message usually occurs when a sheet is referenced in the code that doesn't exist. In your original post you said the source data was in Sheet1 so I wrote the code that way.
Change the lines of my code to where I put Sheet1 to the exact sheet name the raw data resides in.

 

[vcoolio]

Hello Eaje92,

Did you try the code in post #3 ?

Cheerio,
vcoolio.

 

[eaje92]

That message usually occurs when a sheet is referenced in the code that doesn't exist. In your original post you said the source data was in Sheet1 so I wrote the code that way.
Change the lines of my code to where I put Sheet1 to the exact sheet name the raw data resides in.

Hi trebor, I am confused with why the range has to be in column A & B (i.e Range("A:B")) and the part of (find("*")). Could you clarify? Thank you

 

[Trebor76]

I am confused with why the range has to be in column A & B (i.e Range("A:B")) and the part of (find("*")). Could you clarify?

As you didn't say I assumed the number and year data were in columns A and B. If they're not just change that line of code to whatever columns the data is in. You will also have to change the reference to column A in this line of code...

If IsNumeric(Sheets("Sheet1").Range("A" & lngMyRow)) Then

...to whatever column the number is in.