Hiding Undesired Worksheets in Excel 1

[westma]

I have some data on one worksheet which is time-stamped every ten minutes and I can insert as much as I want for any time during a year. Looks as follows:

4/1/02 2:30 26.10 2.57 45 -123.5 225
4/1/02 2:40 23.30 2.51 45 -123.5 225
4/1/02 2:50 24.80 2.35 45 -123.5 225
4/1/02 3:00 24.80 2.62 45 -123.5 225
4/1/02 3:10 25.60 2.41 45 -123.5 225

I also have a worksheet for every month of the year containing tables for analyzing the time-stamped data in each month. How can I write a macro which will hide the unneeded month worksheets? That is, if I only load up data that contains times in January through March on the first worksheet, I only want to be able to see the three worksheets that correspond to these months and make the others inaccessible.

 

[xlbo]

How would excel / the macro know what you have selected ??
Would it have to reference the dates on the 1st worksheet ??
It's probably possible but I need more info about how the data is laid out on your 1st sheet and how this can be interpretted to hide the other sheets

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[westma]

The example I gave above just has data for April 1. However, the sheet could contain data like this that goes through a whole year. My idea was to have a worksheet located in the same workbook named "Jan", a worksheet named "Feb", and so on for every month. Say the example above is located on sheet "RawData" in cells A1:F5. Since the time stamps are just for some times in April, I would only want the worksheet named "April" to be accessible. If there were data from April through June, I would only want the worksheets named with these months to be accessible. I thought the macro could reference the dates in column A on worksheet "RawData", and be able to see which months are needed.
Thanks for your help,
MW

 

[xlbo]

Ok - here we go then
Sub scoobydo()
Dim monthArr(12)
i = 0
With Sheets("RawData&quot
lRow = .Range("A65536&quot.End(xlUp).Row
For Each c In .Range("A2:A" & lRow)
If Format(c.Value, &quot;mmmm&quot <> Format(c.Offset(-1, 0).Value, &quot;mmmm&quot Then
monthArr(i) = Format(c.Value, &quot;mmmm&quot
i = i + 1
Else
End If
Next
End With

For i = 0 To UBound(monthArr())
If monthArr(i) = &quot;&quot; Then Exit Sub
Sheets(monthArr(i)).Visible = False
Next i
End Sub

This assumes that your date data is in col A on sheet RawData and that your sheet names are FULL month names ie January rather than Jan


Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[westma]

This is very close, the problem is that this hides the sheet I want to see and leaves the rest visible-just opposite. Can you switch this? Thanks for your time on this one.

Regards,
MW

 

[xlbo]

D'oh
change:
For i = 0 To UBound(monthArr())
If monthArr(i) = &quot;&quot; Then Exit Sub
Sheets(monthArr(i)).Visible = False
Next i

to:
For each sht in thisworkbook.worksheets
If sht.name <> &quot;RAWData&quot; then sht.visible = false
next
For i = 0 To UBound(monthArr())
If monthArr(i) = &quot;&quot; Then Exit Sub
Sheets(monthArr(i)).Visible = true
Next i


Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[westma]

It also does not seem to like it when there are more than one month's worth of data. It pops up runtime error '9': Subscript out of range and then highlights

Sheets(monthArr(i)).Visible = False

in the debugger.

 

[westma]

wait hold that thought

 

[westma]

OK here's the scoop, now it leaves the sheet I want, but also hides my sheet &quot;RawData&quot;, which I can't have, and it still pops up the error I described above when I put in more than one month. Sorry!

 

[westma]

Alright I was able to get the RawData sheet to appear using

For Each sht In ThisWorkbook.Worksheets
If sht.Name <> &quot;RawData&quot; Then sht.Visible = False
If sht.Name = &quot;RawData&quot; Then sht.Visible = True


Now just the problem of only one month, then I may leave you alone. . .

 

[xlbo]

Did you apply the changes as per my previous post ???
the line:
Sheets(monthArr(i)).Visible = False
does not appear any more

I can run the code and the only time I got the error was when I spelt February incorrectly

Full code should be:
Sub scoobydo()
Dim monthArr(12)
i = 0
With Sheets(&quot;RawData&quot
lRow = .Range(&quot;A65536&quot.End(xlUp).Row
For Each c In .Range(&quot;A2:A&quot; & lRow)
If Format(c.Value, &quot;mmmm&quot <> Format(c.Offset(-1, 0).Value, &quot;mmmm&quot Then
monthArr(i) = Format(c.Value, &quot;mmmm&quot
i = i + 1
Else
End If
Next
End With

For Each sht In ThisWorkbook.Worksheets
If sht.Name <> &quot;RAWData&quot; Then sht.Visible = False
Next
For i = 0 To UBound(monthArr())
If monthArr(i) = &quot;&quot; Then Exit Sub
Sheets(monthArr(i)).Visible = True
Next i

End Sub

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[westma]

Sorry, I posted that before I saw your message. The line highlighted is

Sheets(monthArr(i)).Visible = True

Also, how would the code change if my data started in cel A57 rather than A1? Otherwise the code is the same. Not sure what is going on.

 

[westma]

OK I figured it out and got it working. Many thanks for all your help, it maybe would have worked faster if I weren't such an excelitiot.

Best regards,
MW