Object Required Error, Excel 2000 & 2003
 

Hello,

Why am I getting the Object required Error in the following macro
code?:

Sub HideWorkSheetsC()

Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Dim wsB As Worksheet

Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_ZoneBuildingEquipList.xls")
For Each wsA In wbA.Sheets
For Each cell In wbB.Worksheets("ZONE 5 - BLDG
LIST").Range("D4:D68")
If Right(wsA.Range("C2"), 4).Value = cell Then GoTo nws 'Object
Required Error
If Right(wsA.Range("C2"), 4).Value < cell Then GoTo hws 'Object
Required Error
Next cell
hws:
ws.Visible = xlSheetHidden 'xlSheetVisible
nws:
Next wsA

End Sub

Thenk you for your help,
jfcby

Object Required Error, Excel 2000 & 2003
 

Couple of errors:

1) Right(wsA.Range("C2"), 4).Value doesn't make sense - Right() takes
the .Value of wsa.Range("C2") and returns a 4-character string. The
string doesn't have a .Value property.

2) You're obviously not using Option Explicit at the top of the module
or you'd find that

hws:
ws.Visible = xlSheetHidden

refers to an undefined variable, ws.

I"m not positive, but perhaps this will do what you're looking for:

Public Sub HideWorkSheetsC()
Dim wsA As Worksheet
Dim rTest As Range
Dim sCheck As String

Set rTest = Workbooks("FF_ZoneBuildingEquipList.xls").Workshee ts( _
"ZONE 5 - BLDGLIST").Range("D4:D68")
For Each wsA In Workbooks("Equip_List_FF.xls").Worksheets
sCheck = Right(wsA.Range("C2").Text, 4)
If Application.CountIf(rTest, sCheck) 0 Then _
wsA.Visible = xlSheetHidden
Next wsA
End Sub



In article . com,
jfcby wrote:

Hello,

Why am I getting the Object required Error in the following macro
code?:

Sub HideWorkSheetsC()

Dim wbA As Workbook
Dim wbB As Workbook
Dim wsA As Worksheet
Dim wsB As Worksheet

Set wbA = Workbooks("Equip_List_FF.xls")
Set wbB = Workbooks("FF_ZoneBuildingEquipList.xls")
For Each wsA In wbA.Sheets
For Each cell In wbB.Worksheets("ZONE 5 - BLDG
LIST").Range("D4:D68")
If Right(wsA.Range("C2"), 4).Value = cell Then GoTo nws 'Object
Required Error
If Right(wsA.Range("C2"), 4).Value < cell Then GoTo hws 'Object
Required Error
Next cell
hws:
ws.Visible = xlSheetHidden 'xlSheetVisible
nws:
Next wsA

End Sub

Thenk you for your help,
jfcby

Object Required Error, Excel 2000 & 2003
 

Hello JE McGimpsey,

Thank you for your macro code (works great!), it help me figure out
how to get the following macro code to work. But, I did not know how
to modify your code (still learning how to wright macro's) so I
continued to change the following 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").Workshee ts( _
"ZONE 5 - BLDG LIST").Range("D4:D68")
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 and macro,
jfcby

Object Required Error, Excel 2000 & 2003
 

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").Workshee ts( _
"ZONE 5 - BLDG LIST").Range("D4:D68")
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