Snasui Excel Forum

Code: Select all

Sub CopyData()

Dim i As Integer
Dim lastrow As Integer

lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

If Cells(i, 3).Value <> 0 Then  'To find cells which have value in column C
    Cells(i, 1).Copy
    Cells(i, 3).Copy
    
Worksheets("Sheet2").Range("A2").Paste

End If

Next i

Worksheets("Sheet2").Select
Worksheets("Sheet2").Name = "Cavity"

End Sub

Code: Select all

Sub CopyData()
    Dim i As Long
    Dim lastrow As Long, lastrow2 As Long
    
    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lastrow2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    For i = 2 To lastrow
        If Cells(i, 3).Value <> 0 Then  'To find cells which have value in column C
            Sheets("cavity").Cells(lastrow2, 1).Value = _
                Sheets(1).Cells(i, 1).Value
            Sheets("cavity").Cells(lastrow2, 2).Value = _
                Sheets(1).Cells(i, 3).Value
            lastrow2 = lastrow2 + 1
        End If
    Next i
    
    Sheets(2).Select
    Sheets(2).Name = "Cavity"
End Sub