On Dec 9, 1:16*pm, royUK wrote:
I can't see where you give values to Look_Value, ColNum etc

I would use the VBA .Find method. Maybe

Code:
--------------------
* * Sub LookUpAllSheets()
* Application.ScreenUpdating = False

* Dim wSheet As Worksheet
* Dim tbl * *As Range
* Dim vFound As Range
* Dim sFind *As String
* On Error Resume Next

* sFind = InputBox("Enter search string")
* If Len(sFind) = 0 Then
* MsgBox "No search string entered", vbCritical, "Input required"
* Exit Sub
* End If
* For Each wSheet In ActiveWorkbook.Worksheets
* With wSheet
* Set tbl = .UsedRange
* Set vFound = tbl.Find(sFind, LookIn:=xlValues)
* If Not vFound Is Nothing Then Exit For
* End With
* Next wSheet
* MsgBox vFound.Value
* On Error GoTo 0
* Set tbl = Nothing
* Set wSheet = Nothing
* Application.ScreenUpdating = True

* End Sub
--------------------

--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile:http://www.thecodecage.com/forumz/member.php?userid=15
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=38789

what im trying to do is, to use the code for vlookup to look across
all sheets but narrow it down to some specific sheets.

cant seem to do that yet! Any suggestions?