Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of Contents for Excel File
I have a workbook with 72 worksheets in it. There is a combination of
worksheets used for last year (2006) and this year (2007). Last year I when I created this file I used the code below in the ThisWorkbook section of the VBA code. When the workbook was opened it would hide the other worksheets and just show the Table of Contents for 2006. Now that we are in 2007 I would like it to show both the 2007 and 2006 Table of Contents. Can someone help me modify this script so it will show the 2007 also. Thanks Private Sub Workbook_Open() Dim c As Worksheet For Each c In Sheets If c.Name < "Table Of Contents - 2006" Then c.Visible = False Else: c.Visible = True End If Next c End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of Contents for Excel File
akafrog18, if your sheet is called Table Of Contents - 2007, try this
Dim c As Worksheet For Each c In Sheets If c.Name < "Table Of Contents - 2006" _ And c.Name < "Table Of Contents - 2007" Then c.Visible = False Else: c.Visible = True End If Next c -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "akafrog18" wrote in message ups.com... I have a workbook with 72 worksheets in it. There is a combination of worksheets used for last year (2006) and this year (2007). Last year I when I created this file I used the code below in the ThisWorkbook section of the VBA code. When the workbook was opened it would hide the other worksheets and just show the Table of Contents for 2006. Now that we are in 2007 I would like it to show both the 2007 and 2006 Table of Contents. Can someone help me modify this script so it will show the 2007 also. Thanks Private Sub Workbook_Open() Dim c As Worksheet For Each c In Sheets If c.Name < "Table Of Contents - 2006" Then c.Visible = False Else: c.Visible = True End If Next c End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of Contents for Excel File
Thanks Paul. That did work. What does the '_' do that allows it to
work correctly? I had tried using the Or and And several times but always ended up with a Run Time Error. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of Contents for Excel File
It does nothing as far as making it work, it lets you "split" the line of
code so instead of having a long line like below. You can see it in the VBA window and also helps when posting the code to help avoid "word wrap". If c.Name < "Table Of Contents - 2006" And c.Name < "Table Of Contents - 2007" Then -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "akafrog18" wrote in message oups.com... Thanks Paul. That did work. What does the '_' do that allows it to work correctly? I had tried using the Or and And several times but always ended up with a Run Time Error. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of Contents for Excel File
What does the '_' do
The "_" doesn't do anything in terms of changing the functionality of the code. It simply allows you to write a single logical line of code across two or more physical lines of code. For example, Range( _ "A1" _ ) _ ..Value _ = _ 123 is interpreted by VBA as if it were written on a single line: Range("A1").Value = 123 Note that there must be a space before the underscore character. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "akafrog18" wrote in message oups.com... Thanks Paul. That did work. What does the '_' do that allows it to work correctly? I had tried using the Or and And several times but always ended up with a Run Time Error. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Links picking up values from an older version of linked file | Links and Linking in Excel | |||
How do I view Pivot Table source data file and field names? | Excel Discussion (Misc queries) | |||
Question from MOS file E03C-1-1 , Creating data and contents | Excel Discussion (Misc queries) | |||
save excel file from a table delimited file (.txt) using macros | New Users to Excel |