I just need it to search other work sheets and only populate the answers. Currently it searchs and repopulates the formulas causing it to recalculate.
Sub Find_First()
Dim FindString As String
Dim Rng As Range
Dim MaterialValue As String
Dim SheetNumber As Integer
Dim Counter As Integer
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Status = InputBox("Please Enter the Material Number You want to Search")
If Status = "" Then
MsgBox "Operation Cancelled"
Else:
MaterialValue = Status
SheetNumber = 1
Range("A5").Select
line1:
Counter = 0
SheetNumber = SheetNumber + 1
If SheetNumber > ThisWorkbook.Sheets.Count Then
Worksheets(1).Activate
MsgBox "A & B items are heavly used"
Else
FindString = MaterialValue
Worksheets(SheetNumber).Activate
Counter = Application.WorksheetFunction.CountIf(Range("d:d"), MaterialValue)
If Trim(FindString) <> "" Then
With Sheets(SheetNumber).Range("d:d")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.GoTo Rng, True
End If
End With
For x = 1 To Counter
ActiveCell.Offset(0, -3).Select
Range(ActiveCell, ActiveCell.Offset(0, 18)).Copy
Worksheets("RANKSTER").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Worksheets(SheetNumber).Activate
Cells.FindNext(After:=ActiveCell).Activate
Next
GoTo line1
End If
End If
End If
End Sub
image 2.jpgimage1.jpg
Bookmarks