did not expect any worksheet to have a name following different
pattern than 12-1-2008, etc.
this probably IS the reason for Excel's behavior = run time error
try to replace
If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
Now() _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7)
Now() Then
with
If (DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
Now() _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) Now
()) _
Or ws.Name = "Current Status" Then
and let me know if it worked
also if you don't want the whole process to be activated on workbook
opening than delete the Private Sub Workbook_Open code from
ThisWorkbook module
instead Insert-Module and paste the code "here" under a new name (not
Private Sub Workbook_Open!), say it is "sth"
then Tools-Macro-Macros-find/select your "sth" macro, click on
Options, insert a keyboard shortcut
On 13 Sty, 22:11, Tony S. wrote:
Jarek, Your explanation was perfect. I was able to follow it exactly.
However, after I save and reopen the file, I still get *"Run-time error '13' *
Type mismatch". Does it have anything to do with how the sheets are named? I
can rename them to anything, (like sht1, sht2, sh3, or #1, #2, #3 etc) but I
stiil need one sheet named "Current Status" to be open and active.
Is there a way to hide all the sheets and unhide the "Current Status" sheet?
It doesn't necessarily need to run on opening the file. Ideally, they would
all be hidden until I ran a keyboard macro.
Thanks!
"Jarek Kujawa" wrote:
double-click on ThisWorkbook module
you should see two windows to the right 1. (General) and 2.
(Declarations)
click on 1. (General) and select Workbook
in 2. select "Open" from the list of events
the result should be:
Private Sub Workbook_Open()
End Sub
in between those 2 lines paste the following code:
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
Now() _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7)
Now() Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetVeryHidden
End If
Next ws
save and close yr file. from now on every time you open it and switch
the macros on, the code should do what you expect
HIH
On 13 Sty, 15:58, Tony S. wrote:
Jarek,
When I run this I get "Run-time error '13' *Type mismatch"
I may have pasted the code in the wrong location. I opened the VB editor and
did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under
"Microsoft Excel Objects". Should it be pasted in a module? I am running
Excel 2003.
"Jarek Kujawa" wrote:
the following code will hide/show relevant woksheets on each workbook
opening
press ALT+F11, open ThisWorkbook module, select Workbook_Open and
paste the code
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
* * If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
DateSerial(Year(Now()), Month(Now()), Day(Now())) _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7)
DateSerial(Year(Now()), Month(Now()), Day(Now())) Then
ws.Visible = xlSheetVisible
* * Else
ws.Visible = xlSheetVeryHidden
* * End If
Next ws
End Sub
On 13 Sty, 14:53, Tony S. wrote:
Is there a way to use VB code to hide/show a group of worksheets at once,
possibly using a wildcard? I have a worksheet that contains many sheets that
need to be Veryhidden and Visible quickly and easily without having to go
thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the
year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc).. The
current week will always be visible. *Thanks!- Ukryj cytowany tekst -
- Pokaż cytowany tekst -- Ukryj cytowany tekst -
- Pokaż cytowany tekst -