Hi all,
I have a document that has multiple sheets, I need to search column A using an inputstring to find all values that match them copy them to the MergedData sheet. It needs to excluding "SUB PAYMENT FORM", "Details" and "MergeData" from the search. It needs to search each sheet starting at row 16 working down til last row.
In the merged data sheet i need the data to start being copied at Cell A1, followed by A2 and so on...
I would like for when the button has been click and data copied i would like to have a message box display, stating the sheet names where data has been copied from, i would also like it to display the sheets name of where no data was found.
Below is the code that i have at the moment, it searches all the relevant sheets and copys the data to the MergedData sheet. But it does not give me the message boxes stating where data was found or not.
It also pasted the data in the MergedData sheet starting in row 2 rather that row 1.
Any help you can provide would be greatly appreciated.
Thanks
Aarron
I have a document that has multiple sheets, I need to search column A using an inputstring to find all values that match them copy them to the MergedData sheet. It needs to excluding "SUB PAYMENT FORM", "Details" and "MergeData" from the search. It needs to search each sheet starting at row 16 working down til last row.
In the merged data sheet i need the data to start being copied at Cell A1, followed by A2 and so on...
I would like for when the button has been click and data copied i would like to have a message box display, stating the sheet names where data has been copied from, i would also like it to display the sheets name of where no data was found.
Below is the code that i have at the moment, it searches all the relevant sheets and copys the data to the MergedData sheet. But it does not give me the message boxes stating where data was found or not.
It also pasted the data in the MergedData sheet starting in row 2 rather that row 1.
Any help you can provide would be greatly appreciated.
Thanks
Aarron
Code:
Sub SearchForString()
Dim FirstAddress As String,
WhatFor As String
Dim Cell As Range,
Sheet As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
.CutCopyMode = False
End With
WhatFor = InputBox("What are you looking for?", "Search Criteria")
Worksheets("MergedData").Cells.Clear
If WhatFor = Empty Then Exit Sub
For Each Sheet In SheetsIf Sheet.Name <> "SUB PAYMENT" And Sheet.Name <> "MergedData" And Sheet.Name <> "Details" Then
With Sheet.Columns(1)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.EntireRow.Copy Destination:=Sheets("MergedData").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)Set Cell = .FindNext(Cell)Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End If
Next Sheet
Set Cell = Nothing
End Sub