I'm searching for a criteria "System Name" inside Sheet1, i can find it, i can confirm using 'Debug.Print' but the if statement doesn't accept it as a value

Sub Hostnames()
'
' Hostnames Macro
'


Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(rows.Count, 1).End(xlUp).row

For i = 2 To lastrow
    If Sheet1.Cells(i, 2) = "System Name" Then
    Sheet1.Cells(i, 5).Copy
    erow = Sheet2.Cells(rows.Count, 1).End(xlUp).Offset(1, 0).row
    Sheet1.Paste destination:=Worksheets(“Sheet2”).Cells(erow, 1)
    End If

Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select

End Sub

Print for ex:

CodePudding user response：

Silly question, but the value in your sheet is the same text case you are looking for right? "System Name" and "system name" would not be equal to compare and the if statement would give FALSE.

CodePudding user response：

I believe you have turned off cut/copy mode, and never turn it on.

Give this a shot. I changed the worksheet references for my ease, and I personally find it to be a little better setting the objects at the start of a sub-routine.:

Sub Hostnames()
'
' Hostnames Macro
'


    Dim lastRow As Long, eRow As Long
    Dim firstSheet As Worksheet, secondSheet As Worksheet
    ' Set worksheets objects
    Set firstSheet = ActiveWorkbook.Sheets("Sheet1")
    Set secondSheet = ActiveWorkbook.Sheets("Sheet2")
    ' Get last row of first sheet.
    lastRow = firstSheet.Cells(firstSheet.Rows.Count, 2).End(xlUp).Row
    ' Ensure you can cut/copy/paste
    Application.CutCopyMode = True
    Debug.Print lastRow
    For i = 2 To lastRow
        If firstSheet.Cells(i, 2) = "System Name" Then
            firstSheet.Cells(i, 5).Copy
            eRow = secondSheet.Cells(secondSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            firstSheet.Paste Destination:=secondSheet.Cells(eRow, 1)
        End If
    Next i
    ' Turn off cut/copy mode
    Application.CutCopyMode = False
    ' Autofit columns for both sheets
    secondSheet.Columns().AutoFit
End Sub