Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
Hi jfcby,
try something like ... .... then ws.Visible = xlSheetHidden xlSheetVisible to show again Hope this can help João Rodrigues "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
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:D" & 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
Everything you wanted to know about declaring variables...
http://www.cpearson.com/excel/variables.htm -- HTH... Jim Thomlinson "jfcby" wrote: 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:D" & 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
Are you sure that C2 contains "BLDG_# 2119" and not "BLDG_# " with 2119 in
D2 ??? -- HTH... Jim Thomlinson "jfcby" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 2003
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").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, jfcby |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Worksheets, Excel 200 & 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get rid/hide #NUM! error in Excel 2003? | Excel Worksheet Functions | |||
How to hide zero values in graphs using Mircosoft excel 2003? | Charts and Charting in Excel | |||
How do I hide and unhide worksheets in Excel 2007? | Excel Discussion (Misc queries) | |||
how do I hide data elements in pivot in excel 2003 ? | Excel Worksheet Functions | |||
Excel 2003 Hide PivotItems | Excel Worksheet Functions |