You need to be careful with your references. You are not refering to the
sheets that you think you are in most cases... Watch the dots.

For instance in your with statements you do not preceed the Cells with a dot
so by default the cells will reference the active sheet and not the sheet
specified by the with.

With Workbooks("FF_ZoneBuildingEquipList.xls") _
.Sheets("ZONE 5 - BLDG LIST")
ColD = .Cells(65500, 4).End(xlUp).Row 'Note the dot before cells
End With

In your for loop you reaverse the sheets but within the loop you do not
reference ws so by default you are always looking at the active sheet

CL = Right(Range("C2"), 4)
should be
CL = Right(ws.Range("C2"), 4)

I would fix up your code but I can't tell what you are trying to refence at
any given time and I don't know what your variable types are since you did
not declare them.

Finally if our find does not find something your code will crash. You need
to deal with that using on error statements...
--
HTH...

Jim Thomlinson


"jfcby" wrote:

Hello,

I have 2 workbooks,
Workbook 1: Equip_List_FF.xls has 306 worksheets
Workbook 2: FF_ZoneBuildingEquipList.xls has one worksheet (ZONE 5 -
BLDG LIST).

In Workbook 1 each worksheet.Range("C2") has a number. In Workbook 2
worksheet.range("D4:D") has a list of numbers.

I need a macro to start in workbook 1 sheet 1 Right(Range("C2"), 4) an
check in Workbook 2 sheet ZONE 5 - BLDG LIST Range("D4:D") to find the
number and if the number is not found I need to hide that worksheet.

This is the code I have so far. When I Run the macro it does not hide
worksheets and it does not give a error message.

How can the following macro code be modified to hide a worksheet as
described above?

Sub HideWorkSheet()

With Workbooks("FF_ZoneBuildingEquipList.xls") _
.Sheets("ZONE 5 - BLDG LIST")
ColD = Cells(65500, 4).End(xlUp).Row
End With

With Workbooks("Equip_List_FF.xls")
For Each ws In Worksheets
For t = 4 To ColD
CL = Right(Range("C2"), 4)
CR = Range("D4:D" & ColD).Find(Cells(t, 4), LookIn:=xlValues).Row
If CL < CR Then ws.Visible = False
Next t
Next ws
End With

End Sub

Thank you for your help,
jfcby