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

 

[Andrew Poulsom]

Try:

Sheets(school.Value).Columns("B").EntireColumn.Hidden = True

 

[mainegrl]

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

 

[mainegrl]

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?

 

[mainegrl]

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.

 

[Andrew Poulsom]

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.

 

[mainegrl]

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.

 

[Norie]

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.

 

[mainegrl]

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.

 

[Norie]

And if you need to exclude any sheets then it would be easy to set up some sort of logic to do so.

Did you not see this?