Hi all,
I am searching multiple sheets for a string, once this string is found it copies it to the MergedData sheet.
The problem i am having is it is coping the formulas rather that the value. I have been searching for hours and cannot seem to find a solution.
Here is the code that i am using at the moment.
Thanks Aarron
I am searching multiple sheets for a string, once this string is found it copies it to the MergedData sheet.
The problem i am having is it is coping the formulas rather that the value. I have been searching for hours and cannot seem to find a solution.
Here is the code that i am using at the moment.
Thanks Aarron
Code:
Private Sub CommandButton1_Click()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 = Sheets("SUB CON PAYMENT FORM").Range("L9")
Worksheets("MergedData").Cells.Clear
If WhatFor = Empty Then Exit Sub
For Each Sheet In Sheets
If Sheet.Name <> "SUB CON PAYMENT FORM" And Sheet.Name <> "MergedData" And Sheet.Name <> "Details" Then
With Sheet.Columns(1)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.EntireRow.Copy ActiveWorkbook.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