I have a spreadsheet with a list of codes on separate tabs, each tab
is named according to its category.

On the last tab I have a list containing of ALL the above codes, but
not which category the code is in.

I want a formula to look at all of the tabs (apart from the final one)
and return the category for each code and place it next to the
relevant code in the
list on the last tab.

The end result is that each code in the complete list will have the
correct category next to it.

Any help appreciated.

Hi there

Not entirely sure what you want to do but the code below might be of
some use to you. It will move through each Worksheet in a Workbook
except the one called "Last Tab" (change this in the code to what ever
you want). It will create a range of all the entries in the column A
(providing there are no blanks) in each sheet then it will search for
each cell in that range in the sheet called "Last Tab" when it finds
it the name of the sheet it was originally located on will be placed
in the cell beside the found reference.

Option Explicit
Dim LstCell, MyCell, MyRng As Range
Dim FCell
Dim WkSh As Worksheet

Private Sub CommandButton1_Click()

On Error Resume Next

Sheets("Last Tab").Activate

For Each WkSh In Worksheets

LstCell = WkSh.[A65536].End(xlUp).Address

If WkSh.Name < "Last Tab" Then

Set MyRng = WkSh.Range("A1", LstCell)

For Each MyCell In MyRng

With Sheets("Last Tab")

Set FCell = Cells.Find(What:=MyCell, LookAt:=xlWhole)

If FCell Is Nothing Then

MsgBox MyCell & " not found in Last Tab!"

Resume Next

Else

Cells.Find(What:=MyCell, LookAt:=xlWhole).Activate

ActiveCell.Offset(0, 1).Value = WkSh.Name

End If

End With

Next MyCell

End If

Next WkSh

End Sub

Hope this is of some help to you

S

This is fantastic- I had no idea it would be so involved, thanks for
taking the time to reply!

On Jun 5, 2:30 pm, Incidental wrote:
Hi there

Not entirely sure what you want to do but the code below might be of
some use to you. It will move through each Worksheet in a Workbook
except the one called "Last Tab" (change this in the code to what ever
you want). It will create a range of all the entries in the column A
(providing there are no blanks) in each sheet then it will search for
each cell in that range in the sheet called "Last Tab" when it finds
it the name of the sheet it was originally located on will be placed
in the cell beside the found reference.

Option Explicit
Dim LstCell, MyCell, MyRng As Range
Dim FCell
Dim WkSh As Worksheet

Private Sub CommandButton1_Click()

On Error Resume Next

Sheets("Last Tab").Activate

For Each WkSh In Worksheets

LstCell = WkSh.[A65536].End(xlUp).Address

If WkSh.Name < "Last Tab" Then

Set MyRng = WkSh.Range("A1", LstCell)

For Each MyCell In MyRng

With Sheets("Last Tab")

Set FCell = Cells.Find(What:=MyCell, LookAt:=xlWhole)

If FCell Is Nothing Then

MsgBox MyCell & " not found in Last Tab!"

Resume Next

Else

Cells.Find(What:=MyCell, LookAt:=xlWhole).Activate

ActiveCell.Offset(0, 1).Value = WkSh.Name

End If

End With

Next MyCell

End If

Next WkSh

End Sub

Hope this is of some help to you

S