Hide column on each worksheet generated by macro

[mainegrl]

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?

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
wsTransfer.Select
Range("Database_Transfer").AdvancedFilter xlFilterCopy, Range("Criteria"), _
    wsList.Range("Database_Unique")
 
'create named range for use if new worksheet needed
wsList.Select
Set rng = Range("Database_Unique")
 
'extract a list of schools
wsList.Range("B6:B286").Copy _
  Destination:=Range("L6")
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
        Sheets(school.Value).Cells.Clear
        rng.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Sheets("Student List").Range("L6:L7"), _
            CopyToRange:=Sheets(school.Value).Range("A6"), _
            Unique:=False
 
    'if worksheet doesn't exist, add new sheet and run advanced filter
    Else
        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"), _
            Unique:=False
 
    End If
 
    'sort student list
    Sheets(school.Value).Select
    Sheets(school.Value).Range("A6:E50").Select
    Selection.Sort _
        Key1:=Range("A7"), Order1:=xlAscending, _
        Key2:=Range("C7"), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
 
    'add header
    If IsEmpty(Sheets(school.Value).Range("a1")) Then
        wsList.Select
        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
Columns("B").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
 
Next
 
'delete criteria area
wsList.Select
wsList.Columns("J:L").Delete
 
'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
    wSheet.Select
    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

 

[mainegrl]

Well, yes. But I figured it was more efficient to have the code in a loop that only affects the worksheets that need Col B hidden than adding it in a loop affecting every worksheet in the workbook and then having to (re)test for which sheets to apply it to.

 

[Norie]

What do you mean by 'retest'?

I mean you are already looping through all the worksheets in the first place.

Sure you would have to test for each worksheet but I don't see any problem with that.

 

[mainegrl]

Under normal circumstances, there wouldn't be a problem and it would be easy to do.

But in my case, I have a bunch of code that only gets applied to these worksheets created by the macro (loop 1), and another bunch of code that gets applied to every worksheet (loop 2).

If I added the hiding Col B code to the second loop I would have to specify which sheets not to apply it to. There are other worksheets that currently exist and that will be added manually over the life and use of the workbook (not through the macro), so I couldn't simply tell it every worksheet but X. I don't know yet all the worksheets not to apply it to. So I would have to apply the code to only the macro created sheets, which I have already determined by the first loop. Hence retesting for something I already know and adding an unecessary process to the macro.

Otherwise, a good idea. Just not practical for my workbook.