VBA not working fully

[ausswe]

I have a shared Excel spreadsheet that many colleagues use and they change column size, zoom etc so I have created the following VBA code (connected to a button) so that it easily can be restored but for some reasons the column width wont work properly. In the sheet named "TOP" I want columns A:AJ to have 15 in width and AK:AL have 75 but for some reason Excel resizes AF:AL to 75 when it runs.
Columns("A:AJ").Select
Selection.ColumnWidth = 15
Columns("AK:AL").Select
Selection.ColumnWidth = 75

Any ideas why this happens? I have tried to add steps in the VBA where it moves to another sheet and comes back to the original sheet but no success.

Sub ResetSheets()
'
' ResetSheets Macro
'

'
    Application.Calculation = xlManual
    Sheets("TOP").Select
    ActiveWindow.Zoom = 100
    Columns("A:AJ").Select
    Selection.ColumnWidth = 15
    Columns("AK:AL").Select
    Selection.ColumnWidth = 75
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "TOTALT"
    Range("B5").Select
    Selection.ClearContents
    Range("D5").Select
    Selection.ClearContents
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "100"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "JA"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "JA"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "NEJ"
    Range("C:C").EntireColumn.Hidden = True
    Range("A5").Select
    ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFormattingColumns:=True
    Sheets("SÖK").Select
    ActiveWindow.Zoom = 100
    Columns("A:N").Select
    Selection.ColumnWidth = 15
    Range("B5").Select
    Selection.ClearContents
    Range("C:C").EntireColumn.Hidden = True
    ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    Sheets("UPPF").Select
    ActiveWindow.Zoom = 100
    ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFiltering:=True
    Sheets("NYA").Select
    ActiveWindow.Zoom = 100
    ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=False
    Sheets("Data").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("INFO").Select
    ActiveWindow.Zoom = 100
    Range("A1").Select
    Application.Calculation = xlAutomatic

End Sub

 

[Joe4]

You can clean up your code a bit. You have a lot of unnecessary "Select" statements, probably gotten by using the Macro Recorder.
See if this update version works any better:

Sub ResetSheets()
'
' ResetSheets Macro
'

'
    Application.EnableEvents = False
    Application.Calculation = xlManual
    
    Sheets("TOP").Select
    ActiveWindow.Zoom = 100
    Columns("A:AJ").ColumnWidth = 15
    Columns("AK:AL").ColumnWidth = 75
    Range("A5").FormulaR1C1 = "TOTALT"
    Range("B5").ClearContents
    Range("D5").ClearContents
    Range("E5").FormulaR1C1 = "100"
    Range("F5").FormulaR1C1 = "JA"
    Range("G5").FormulaR1C1 = "JA"
    Range("H5").FormulaR1C1 = "NEJ"
    Range("C:C").EntireColumn.Hidden = True
    Range("A5").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFormattingColumns:=True
        
    Sheets("SÖK").Select
    ActiveWindow.Zoom = 100
    Columns("A:N").ColumnWidth = 15
    Range("B5").ClearContents
    Range("C:C").EntireColumn.Hidden = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True
    
    Sheets("UPPF").Select
    ActiveWindow.Zoom = 100
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, AllowFiltering:=True
    
    Sheets("NYA").Select
    ActiveWindow.Zoom = 100
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=False
    
    Sheets("Data").Select
    ActiveWindow.SelectedSheets.Visible = False
    
    Sheets("INFO").Select
    ActiveWindow.Zoom = 100
    Range("A1").Select
    
    Application.Calculation = xlAutomatic
    Application.EnableEvents = True

End Sub

If you still have that problem after you run this code, answer these two questions:
1. Do you have any protected ranges on your "Top" sheet?
2. Do you have any merged cells in your "Top" sheet?

 

[mumps]

Try:

Sub ResetSheets()
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    Sheets("TOP").Activate
    ActiveWindow.Zoom = 100
    With ActiveSheet
        .Range("A:AJ").ColumnWidth = 15
        .Columns("AK:AL").ColumnWidth = 75
        .Range("A5") = "TOTALT"
        .Range("B5,D5").ClearContents
        .Range("E5").Resize(, 4).Value = Array("100", "JA", "JA", "NEJ")
        .Range("C:C").EntireColumn.Hidden = True
        .Protect
    End With
    Sheets("SÖK").Activate
    ActiveWindow.Zoom = 100
    With ActiveSheet
        .Columns("A:N").ColumnWidth = 15
        .Range("B5").ClearContents
        .Range("C:C").EntireColumn.Hidden = True
        .Protect
    End With
    Sheets("UPPF").Activate
    ActiveWindow.Zoom = 100
    ActiveSheet.Protect
    Sheets("NYA").Activate
    ActiveWindow.Zoom = 100
    ActiveSheet.Protect
    Sheets("Data").Visible = False
    Sheets("INFO").Activate
    ActiveWindow.Zoom = 100
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With
End Sub

 

[ausswe]

Thanks to you both @Joe4 and @mumps
Joe: you are correct I have used the Macro Record to figure out some of it. Your code seems to be working fine. The only obstacle was, but not related to you code, hiding the sheet "Data" where it fails when the sheet isn't visible (which it isn't all the time).

I just want to make sure it is hidden every time the settings are reset, is there an alternative to the code so that it hides the sheet if it turns out it is left visible?

   Sheets("Data").Select
    ActiveWindow.SelectedSheets.Visible = False

mumps: you code didn't want to work for some reason but I'm going to check through it and see if I can figure out why.

 

[Joe4]

In your VBA code, you can unhide the sheet (if it is hidden), do what you need to do, and then re-hide it.
That can all be programmed right into your code.

 

[ausswe]

Thanks Joe4 - I figured out a VBA code that will check and hide it if it is visible (so that someone doesn't forget to hide it manually).
If someone needs the code I used the following code that worked:

    If Sheets("Data").Visible = True Then
     Sheets("Data").Select
     ActiveWindow.SelectedSheets.Visible = False
    End If