Macro - Return row of data to sheet2 from keyword on sheet 1

[caudiffred]

I have the below code that returns what I want. But it returns it to Column A. How do I get it to return the data starting in Column B?

Thank you,

Clint

Sub EFP()
    Dim keyword As String: keyword = Sheets("Results").Range("B3").Value
    Dim countRows1 As Long, countRows2 As Long
    countRows1 = 3 'the first row of my dataset in the Data tab
    endRows1 = 500 'the last row of my dataset in the Data tab
    countRows2 = 6 'the first row where I want to start writing the found rows
    For j = countRows1 To endRows1
        If Sheets("Data").Range("B" & j).Value = keyword Then
            Sheets("Results").Rows(countRows2).Value = Sheets("Data").Rows(j).Value
            countRows2 = countRows2 + 1
        End If
    Next j
End Sub

 

[caudiffred]

I can also attach the spreadsheet if needed.

 

[AlphaFrog]

Change the two 10s to how many columns of data you want copied.

Sub EFP()
    Dim keyword As String: keyword = Sheets("Results").Range("B3").Value
    Dim countRows1 As Long, countRows2 As Long
    countRows1 = 3 'the first row of my dataset in the Data tab
    endRows1 = 500 'the last row of my dataset in the Data tab
    countRows2 = 6 'the first row where I want to start writing the found rows
    For j = countRows1 To endRows1
        If Sheets("Data").Range("B" & j).Value = keyword Then
            [B]Sheets("Results").Range("B" & countRows2).Resize(,[COLOR="#FF0000"]10[/COLOR]).Value = Sheets("Data").Range("A" & j).Resize(,[COLOR="#FF0000"]10[/COLOR]).Value[/B]
            countRows2 = countRows2 + 1
        End If
    Next j
End Sub

 

[My Aswer Is This]

This script is copying the entire row.
You say you want to start copying from column B
We need to know from column b to column ?
Is it B to G:
Or B to P

???

 

[caudiffred]

I am sorry, What I meant was, I still want it to copy the entire row, but when is inserts/pastes it onto sheet 2 I would like it to start inserting/pasting starting at Column B. With the current code, it inserts/pastes it into Row 6, Column A.

 

[iliace]

Try this instead:

Sub EFP()
    Dim keyword As String
    Dim countRows1 As Long, countRows2 As Long
    
    Dim rngData As Excel.Range
    Dim iColSize As Long
    
    keyword = Sheets("Results").Range("B3").Value
    countRows1 = 3 'the first row of my dataset in the Data tab
    endRows1 = 500 'the last row of my dataset in the Data tab
    countRows2 = 6 'the first row where I want to start writing the found rows
    
    For j = countRows1 To endRows1
        If Sheets("Data").Range("B" & j).Value = keyword Then
            With Sheets("Data")
              Set rngData = Intersect(.Rows(j), .UsedRange)
            End With
            With rngData
              iColSize = .Columns.Count
              iRowSize = .Rows.Count
              Sheets("Results").Cells(countRows2, 2).Resize(iRowSize, iColSize).Value = .Value
            End With
            countRows2 = countRows2 + 1
        End If
    Next j
End Sub

 

[caudiffred]

Not sure if this is the correct way or not, but I got it to do what I wanted with this.

Sub EFP()
    Dim keyword As String: keyword = Sheets("Results").Range("B3").Value
    Dim countRows1 As Long, countRows2 As Long
    countRows1 = 3 'the first row of my dataset in the Data tab
    endRows1 = 500 'the last row of my dataset in the Data tab
    countRows2 = 6 'the first row where I want to start writing the found rows
    For j = countRows1 To endRows1
        If Sheets("Data").Range("B" & j).Value = keyword Then
            Sheets("Results").Range("B" & countRows2 & ":Z" & countRows2).Value = Sheets("Data").Range("A" & j & ":Y" & j).Value
            countRows2 = countRows2 + 1
        End If
    Next j
End Sub

 

[My Aswer Is This]

Glad to see you answered your own question

 

[caudiffred]

Well thanks to AlphaFrog for the guidance to my answer!