search "date :" in all sheets except sheet1 then paste the value to sheet1

Question

Please kindly finish this code.

I would to search for the word "Date :" in all sheets(sheet2 to sheets...) and if found, copy the value to sheet1

Ex : if found the word "date :" in sheet2 column A1, then copy A2 value and paste to sheet1 column A1. copy and paste sheet3 to sheet1 column B1. process it in order

Sub help()
Dim SearchString As String
Dim SearchRange As Range, cl As Range
Dim ws As Worksheet

    SearchString = "Date :"
    For Each ws In ActiveWorkbook.Worksheets
    Set ws = ws.Cells.Find(What:=SearchString, _
        After:=sh.Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)
End Sub

Thank you so much

Jagadish Dabbiru Jagadish Dabbiru · Accepted Answer · 2014-08-05T18:15:23

Let me know if this works for you

Sub help()
Dim SearchString As String
Dim cell As Range
Dim ws As Integer

    SearchString = "Date :"
    For ws = 2 To ActiveWorkbook.Worksheets.Count
    UsedRng = Sheets(ws).UsedRange.Address
    For Each cell In Range(UsedRng).Cells
    If cell.Value = SearchString Then
        Sheets(1).Cells(1, ws - 1).Value = cell.Offset(0, 1).Value
        Exit For
    End If

    Next

    Next

End Sub

search "date :" in all sheets except sheet1 then paste the value to sheet1

1 Answers