I have multiple sheets where I go through a loop. It looks for specific keywords. If found then take value where that keyword was found and insert it in "Mastersheet". I tried to change it so when the value was found the whole row of that value gets copied in "Mastersheet" but I cant get it to work. Each row should be copied below each other. Maybe my code can be modified.
VBA Code:
Sub count()
totalsheets = Worksheets.count
mykeyword = Worksheets("Mastersheet").Cells(2, 19).Value
mykeyword2 = Worksheets("Mastersheet").Cells(3, 19).Value
For i = 1 To totalsheets
If Worksheets(i).Name <> "Mastersheet" Then
lastRow = Worksheets(i).Cells(Rows.count, 1).End(xlUp).Row
lastCol = Worksheets(i).Cells(Columns.count, 1).End(xlUp.Column
For j = 8 To lastRow
If Worksheets(i).Cells(j, 1).Value = mykeyword Then
Worksheets("Mastersheet").Activate
lastRow = Worksheets("Mastersheet").Cells(Rows.count, 1).End(xlUp).Row
lastCol = Worksheets("Mastersheet").Cells(Rows.count, 1).End(xlUp).Column
Worksheets("Mastersheet").Cells(lastRow + 1, 1).Value = Worksheets(i).Cells(j, 1).Value
ElseIf Worksheets(i).Cells(j, 1).Value = mykeyword2 Then
Worksheets("Mastersheet").Activate
lastRow = Worksheets("Mastersheet").Cells(Rows.count, 1).End(xlUp).Row
Worksheets("Mastersheet").Cells(lastRow + 1, 1).Value = Worksheets(i).Cells(j, 1).Value
End If
Next
End If
Next
End Sub