Hide Worksheets, Excel 200 & 2003

[jfcby]

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") 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") 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" & 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

 

[Jim Thomlinson]

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...

 

[JoaoRodrigues]

Hi jfcby,

try something like ...
.... then ws.Visible = xlSheetHidden
xlSheetVisible to show again

Hope this can help

João Rodrigues

 

[jfcby]

Hello Jim,

Thank you for your help. I made the changes as you describe and now
the code is hiding all my worksheets.

The problem is this part of the code CL = Right(ws.Range("C2"), 4).
Each cell value is formated "General" and the data is "BLDG_# 2119".
When I used Msgbox to see what "CL" value is this was the result "_#".
When I used ws.Range("C2") "CL" value was "BLDG_#". How does the cell
need to formated so that "CL" will be "2119"?

jfcby

 

[jfcby]

Hello Jim,

Thank you for your help. I'm not sure that I understand what you are
asking for but I'll give it a try.

The following code has notes through them describing what they
reference:

Sub HideWorkSheet()

With Workbooks("FF_ZoneBuildingEquipList.xls") _
.Sheets("ZONE 5 - BLDG LIST")
ColD = .Cells(65500, 4).End(xlUp).Row 'cell values are only
numbers formated as text
End With

With Workbooks("Equip_List_FF.xls")
For Each ws In Worksheets
For t = 4 To ColD
CL = Right(Range("C2"), 4) 'cell value is text & numbers formated
as General
CR = Range("D4" & ColD).Find(Cells(t, 4), LookIn:=xlValues).Row
'cell values are only numbers formated as text
If CL <> CR Then ws.Visible = False
Next t
Next ws
End With

End Sub

I'm not sure how to declare varible types. Could you give me an
example of what a varible is and how to declare it?

jfcby

 

[Jim Thomlinson]

Everything you wanted to know about declaring variables...

http://www.cpearson.com/excel/variables.htm

 

[Jim Thomlinson]

Are you sure that C2 contains "BLDG_# 2119" and not "BLDG_# " with 2119 in
D2 ???

 

[jfcby]

Hello Jim,

Thank you for your help. I was unsuccessful in getting the macro in
the above previous post to work. So I completely changed the macro.

Sub HideWorkSheetsC()

Dim wbA As Workbook
Dim wsA As Worksheet
Dim ceTa As String
Dim ceTb As String

Set wbA = Workbooks("Equip_List_FF.xls")
For Each wsA In wbA.Sheets
For Each cell In
Workbooks("FF_ZoneBuildingEquipList.xls").Worksheets( _
"ZONE 5 - BLDG LIST").Range("D468")
ceTa = Right(Range("A2").Text, 4)
ceTb = Right(Range("C2").Text, 4)
If ceTa = cell Or ceTb = cell Then GoTo nws
Next cell
wsA.Visible = xlSheetHidden
nws:
Next wsA

End Sub

Thank you for your help,
jfcby

 

[jfcby]

Hello,

Corrections made to the above macro code:

Sub HideWorkSheetsC()

Dim wbA As Workbook
Dim wsA As Worksheet
Dim ceTa As String
Dim ceTb As String

Set wbA = Workbooks("Equip_List_FF.xls")
For Each wsA In wbA.Sheets
For Each cell In
Workbooks("FF_ZoneBuildingEquipList.xls").Worksheets( _
"ZONE 5 - BLDG LIST").Range("D468")
ceTa = Right(wsA.Range("A2").Text, 4)
ceTb = Right(wsA.Range("C2").Text, 4)
If ceTa = cell Or ceTb = cell Then GoTo nws
Next cell
wsA.Visible = xlSheetHidden 'xlSheetVisible
nws:
Next wsA

End Sub

jfcby