macro to identify columns to hide

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

winxp pro office 2003

I have a record that has the name of 7 ws (from a master workbook of 62
ws) as 7 fields in the record.

I need to pass these 7 ws names to a macro that will then run on the
master wb to hide all cols but these 7 and then show me the revised wb

I sure don't know how to do this, or even if this is the best approach
to my problem. Maybe this is not even possible in a macro and I need to
use VBA

Much gratitude for your time and expertise

Joanne
 
You talk about hiding columns, but say the 7 names refer to worksheets.

Assume you want to hide worksheets and not columns. Assume the list of 7
worksheets is in a single row starting in B2 (B2:H2) in a worksheet named
Master.

Also, people generally see the term MACRO and VBA as being equivalent.

Sub HideSheets()
Dim rng As Range, v As Variant
Dim sh As Worksheet, s As String
Dim i As Long, bFound As Boolean
With Worksheets("Master")
Set rng = Range("B2").Resize(1, 7)
v = rng.Value
End With
For Each sh In Worksheets
If LCase(sh.Name) <> "master" Then
bFound = False
s = LCase(sh.Name)
For i = LBound(v, 2) To UBound(v, 2)
If s = LCase(v(1, i)) Then
bFound = True
Exit For
End If
Next i
If Not bFound Then
sh.Visible = xlSheetHidden
Else
sh.Visible = xlSheetVisible
End If
End If
Next sh
End Sub
 
Tom
Thank you for your prompt reply. You are right, I meant worksheets and
not columns. Also, I always thought (though most of my macro/vba
knowledge is in Access and Word) that macros and vba were quite
different. In access there is even a feature to transpose your macros to
vba. Gave me the idea they were quite different I guess.

You made short work of what I have been sweating for days. I do have a
couple questions to help me understand the code please.

What does .Resize(1,7) do. There will often be other numbers of ws than
7, so I am hoping that this does not lock the range to 7 elements.

What is happening with LBound(v,2) to UBound (v,2) I think it is setting
the upper and lower bounds of the array but I am wondering what job v,2
is doing in the routine

Also, why do you use LCase in the code?

Just trying to learn so that next time I can be of more help to myself,
or perhaps someday be able to help someone else

Thank you for your time and consideration of my
 
Tom
As I study your code I am wondering if I was as clear as I needed to be
in presenting my problem. Either that or I do not understand how the
code is doing what I need.

The 7 ws names are in a record on a table in one wb, called AlphaForm.
They need to be compared to the 62 ws names that are in a wb called
Master. If they are not in the record on AlphaForm, then they need to be
hidden on the ws in Master wb.

I understand the iteration thru the cells and test test to see if the ws
is present in the cells B2:H2, but it looks like it all is happening on
one wb instead of comparing the record from AlphaForm with the ws
collection in Master wb.

I would like to understand the code and how it works, not just use your
knowledge and then continue to be clueless if I want to use this type of
coding on some other project. Please help me understand the code
Thank You
Joanne
 
Hello Joanne

I add comments to Tom's code:

Sub HideSheets()
Dim rng As Range, v As Variant
Dim sh As Worksheet, s As String
Dim i As Long, bFound As Boolean

'make an array with the 7 sheets to be visible, store it in v
With Worksheets("AlphaForm")
Set rng = Range("B2").Resize(1, 7)
v = rng.Value
End With

'loop through all the sheets
For Each sh In Worksheets

'start with the fact that the sheet is not found yet
'(prior to looping through the sheet names in the array)
bFound = False

'use s to compare
s = LCase(sh.Name)

'loop though sheet names in the array
For i = LBound(v, 2) To UBound(v, 2)

'if both names match
If s = LCase(v(1, i)) Then

'we found that this sheet should be visible
bFound = True
Exit For
End If
Next i

'if sheet's name was found, hide the sheet
If Not bFound Then
sh.Visible = xlSheetHidden
Else
sh.Visible = xlSheetVisible
End If

Next sh
End Sub
 
Given your post that the list of worksheets is in a workbook named
alphaform.xls in an unnamed sheet beginning in an unnamed cell, for purposes
of the code I will assume it is in a sheet named Sheet1 and starts in Cell B2
and is contained in a set of contiguous cells to B2 in the same row and
extending to the right for an unknown quantity (but there will always be at
least B2 and C2 filled).

Sub HideSheets()
Dim rng As Range, v As Variant
Dim sh As Worksheet, s As String
Dim i As Long, bFound As Boolean
' get the list of worksheets
With Workbooks("Alphaform.xls").Worksheets("Sheet1")
Set rng = .Range("B2")
Set rng = .Range(rng, rng.End(xlToRight))
' put the list of sheets in a 2 dimensional array
' of size first dimenaion: 1 to 1
' second dimension: 1 to n
v = rng.Value
End With
For Each sh In Workbooks("Master.xls").Worksheets
' use a boolean variable to indicate whether the
' sh is found in the list of sheets
bFound = False
s = LCase(sh.Name)
' loop across the 2nd dimension of the sheet list
For i = LBound(v, 2) To UBound(v, 2)
If s = LCase(v(1, i)) Then
' sh is in the list
bFound = True
' no need to continue checking
Exit For
End If
Next i
If Not bFound Then
' sheet wasn't in list and should be hidden
sh.Visible = xlSheetHidden
Else
' sheet was in list so make sure it is visible
sh.Visible = xlSheetVisible
End If
Next sh
End Sub

In Access, macros are different from VBA - not really the case in Excel. '

I use Lcase so I am comparing all lower case to all lower case. Then if you
type in the name and it doesn't match solely because of case, this isn't an
impediment.
 
Tom
Thanks so much for the code. I understand pretty much of it, but I still
am wondering what v,2 does. I know that v is the variable that holds the
name of the wss that need to be opened, but what is the 2 for?

Thank you very much for sharing your knowledge with me and the rest of
the newsgroup. There is much to learn about Excel and I'm convinced that
this is the place to do it. You MVPs deserve a round of applause.

I will get this code into my module in a day or two and give it a test
run. I'm anxious to see it work. Will report back with it's success.

Thanks
Joanne
 
Back
Top