hondahawkrider
New Member
- Joined
- Nov 12, 2015
- Messages
- 12
I have some VBA code that works great.. It loops thru my worksheets looking for the value "Red Hat" and copies that data to worksheet called "Summary" ... However, I would also like it to put the name of the worksheet name where the data ("Red Hat") was found as well . I am having a hard time adding it to the loop .. .it's been a while since Ive need to to do any vba programminmg ... any help is appreciated ...
VBA Code:
Sub RedHatCopyRowsBasedOnPartialMatch()
Dim ws As Worksheet, summarySheet As Worksheet
Dim lastRow As Long, i As Long, cellValue As String
' Set the summary sheet where you want to paste data
Set summarySheet = ThisWorkbook.Worksheets("Summary")
summarySheet.Range("A1").ClearContents ' Clear existing data on summary sheet
' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then ' Skip the summary sheet itself
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row ' Find last row with data
For i = 1 To lastRow
cellValue = ws.Cells(i, "A").Value ' Get value from column A
' Check if the cell value contains your target partial string (modify "PART" as needed)
' If InStr(1, cellValue, "PART") > 0 Then
If InStr(1, cellValue, "Red Hat") > 0 Then
ws.Rows(i).Copy summarySheet.Cells(summarySheet.Rows.Count, "A").End(xlUp).Offset(1, 0) ' Paste to summary sheet
End If
Next i
End If
Next ws
End Sub