Hide column on each worksheet generated by macro


New Member
Feb 26, 2009
I have a macro that looks at a list of students and the school they attend, and then creates an individual worksheet for each school containing only their students. I want to hide Column B on each of those worksheets. This seems like it should be easy, but it is not working.

I have pasted the entire macro for context. The problem code is in red.

When I leave it as is pasted below, Column B does not get hidden.

When I change it to <CODE>Sheets(school.Value).Columns("B").Select</CODE> I get an error message stating "Select method of Range class failed."

Anyone have any ideas?

Rich (BB code):
Sub ExtractSchools()
Dim wsTransfer As Worksheet     'worksheet with transferred data from registrations wrkbk
Dim wsList As Worksheet         'worksheet with list of students
Dim wsNew As Worksheet          'worksheet being added for a school
Dim wSheet As Worksheet         'name to loop through all worksheets 
Dim rng As Range
Dim school As Range
Dim rowNum As Integer
'set variables for transfer to clean list
Set wsTransfer = Sheets("Transfer")
Set wsList = Sheets("Student List")
'filter out zero values
Range("Database_Transfer").AdvancedFilter xlFilterCopy, Range("Criteria"), _
'create named range for use if new worksheet needed
Set rng = Range("Database_Unique")
'extract a list of schools
wsList.Range("B6:B286").Copy _
wsList.Range("L6:L286").AdvancedFilter _
  Action:=xlFilterCopy, _
  CopyToRange:=Range("J6"), Unique:=True
rowNum = Cells(Rows.Count, "J").End(xlUp).Row
'set up temporary criteria area
Range("L6").Value = Range("B6").Value
'generate updated student list for each school
For Each school In Range("J7:J" & rowNum)
    'add the school name to the criteria area
    wsList.Range("L7").Value = school.Value
    'if worksheet exists, clear old data and run advanced filter
    If WksExists(school.Value) Then
        rng.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheets("Student List").Range("L6:L7"), _
            CopyToRange:=Sheets(school.Value).Range("A6"), _
    'if worksheet doesn't exist, add new sheet and run advanced filter
        Set wsNew = Sheets.Add
        wsNew.Move After:=Worksheets(Worksheets.Count)
        wsNew.Name = school.Value
        rng.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheets("Student List").Range("L6:L7"), _
            CopyToRange:=wsNew.Range("A6"), _
    End If
    'sort student list
    Selection.Sort _
        Key1:=Range("A7"), Order1:=xlAscending, _
        Key2:=Range("C7"), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    'add header
    If IsEmpty(Sheets(school.Value).Range("a1")) Then
        Range("A1:A3").Copy Destination:=Sheets(school.Value).Range("A1:A3")
        Range("A4:E5").Copy Destination:=Sheets(school.Value).Range("A4:E5")
    End If
    'insert label reflecting school name
    Sheets(school.Value).Range("A5") = "=B7"
'hide school repetition in column B
Selection.EntireColumn.Hidden = True
'delete criteria area
'format worksheets
For Each wSheet In ActiveWorkbook.Worksheets
    'set col width
    wSheet.Columns(1).ColumnWidth = 25
    wSheet.Columns(2).ColumnWidth = 30
    wSheet.Columns(3).ColumnWidth = 25
    wSheet.Columns(4).ColumnWidth = 30
    wSheet.Columns(5).ColumnWidth = 30
    'set row height
    wSheet.Range("A1:A2").RowHeight = 22
    wSheet.Range("A3:A3").RowHeight = 30
    wSheet.Range("A4:A200").RowHeight = 22
    'hide gridlines
    ActiveWindow.DisplayGridlines = False
    'set page setup options
    With ActiveSheet.PageSetup
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next wSheet
End Sub
Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Thanks for the super quick response! I just pasted the code you suggested and it didn't hide the column. At least it didn't generate an error :)
Upvote 0
One thought - I do have the contents of cell A4 centering across A4:E4, although the cells are not merged. Same with Row 5. Could this be affecting it?
Upvote 0
Sucess! The code you suggested works beautifully. The problem was that I had a line later in the macro setting the column width of B to 30 so it overrided the hiding. Muchas gracias.
Upvote 0
To test my code I opened a new workbbook with one sheet named Sheet1. In A1 I entered the name of the worksheet. In A4 I added some text and centred it across A4:E4. This hid column B:

Sub Test()
    Dim school As Range
    Set school = Range("A1")
    Sheets(school.Value).Columns("B").EntireColumn.Hidden = True
End Sub

So I don't know why it doesn't work for you, sorry.
Upvote 0
Our messages crossed. I'm set now. What you suggested works perfectly. I just hadn't noticed the code I had changing the column width.
Upvote 0
Why not put the code to hide the column(s) in the loop where you are formatting worksheets?

Then you could refer to the sheet(s) with the variable wSheet.:)

And if you need to exclude any sheets then it would be easy to set up some sort of logic to do so.
Upvote 0
The only reason for not hiding column B in the formatting worksheets loop is that that loop affects all worksheets in the book. Besides the worksheets being created by the macro, there is also a worksheet that has data transferred from another workbook. I want to be able to see the contents of column B in that worksheet as that is how the macro determines what worksheets to create and if it's not visible I won't be able to visually tell (for my short attention span) what schools/worksheets I should have.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back