Hide column on each worksheet generated by macro

mainegrl

New Member
Joined
Feb 26, 2009
Messages
14
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
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
 
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.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top