Very sloppy VBA code in workbook

[g1terra]

I have some code that simply hides rows on multiple identical sheets. the only issue I have is when i open the sheet it takes 30 seconds to open the file. I would like this file in NON XLSM format because when saving in this format it always asks to rename the file on save. Also this sheet will be used in Excel 2003.. When I created for just one sheet and used code cleaner it cleaned up the file and opened quickly. Now that i have the entore workbook together code cleaner does not help the loading on startup. I prefer not to calculate on save if i can avoid this as well. Any comments to steer a slightly better than novice in the right direction.

 

[jolivanes]

Similar to what people previously suggested. Something like this would replace that first part of your code

Sub Maybe()
Dim shArr, i As Long

shArr = Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12")

For i = LBound(shArr) To UBound(shArr)
    With Sheets(shArr(i))
        .Rows("6:283").EntireRow.Hidden = False
    With Sheets(shArr(i) & " Balance")
        .Rows("5:23").EntireRow.Hidden = False
    End With
    End With
Next i
End Sub

 

[g1terra]

For SOme reason part of my code worked for all sheets (ex P1, P2, P3 etc) but the following did not (P1 Balance, P2 Balance etc). Here is my cleaned up code

Sub FOH_8_6()
'
' FOH_8_6 Macro
'


Application.ScreenUpdating = False

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next

Set rng = ActiveCell
Sheets("Weekly Valid").Select
Columns("C:AY").EntireColumn.Hidden = False
Columns("D:E").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = True
Columns("J:K").EntireColumn.Hidden = True
Columns("M:N").EntireColumn.Hidden = True
Columns("P:Q").EntireColumn.Hidden = True
Columns("S:T").EntireColumn.Hidden = True
Columns("V:W").EntireColumn.Hidden = True
Columns("Y:Z").EntireColumn.Hidden = True
Columns("AB:AC").EntireColumn.Hidden = True
Columns("AE:AF").EntireColumn.Hidden = True
Columns("AH:AI").EntireColumn.Hidden = True
Columns("AK:AL").EntireColumn.Hidden = True
Columns("AN:AO").EntireColumn.Hidden = True
Columns("AQ:AR").EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 2
Columns("AT:AU").EntireColumn.Hidden = True
Columns("AW:AX").EntireColumn.Hidden = True
Columns("AA:AD").EntireColumn.Hidden = True
Range("A1").Select
Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12")). _
Select
Sheets("P1").Activate
Rows("6:283").Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=21
Rows("29:32").EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=51
Rows("85:88").EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=54
Rows("141:144").EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=51
Rows("197:200").EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=57
Rows("253:256").EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=39
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("B6").Select
Sheets("P1 Balance").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("P1 Balance").Select
Sheets(Array("P1 Balance", "P2 Balance", "P3 Balance", "P4 Balance", "P5 Balance", _
"P6 Balance", "P7 Balance", "P8 Balance", "P9 Balance", "P10 Balance", "P11 Balance" _
, "P12 Balance")).Select
Sheets("P1 Balance").Activate
Rows("5:23").EntireRow.Hidden = False
Rows("14:15").EntireRow.Hidden = True
Range("C5").Select
Sheets("P12 Balance").Select
Application.Goto rng
Application.ScreenUpdating = True


For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect Password:=unpass
Next
Exit Sub




End Sub

 

[g1terra]

I left the Active window scroll because when I deleted it and ran the macro it finished at the bottom of the sheet.

 

[Michael M]

Check the P1 Balance, etc for leading trailling spaces in the tab name !!
AND
if you din't select rows / columns, you wouldn't have to scroll !!!

Sub FOH_8_6()
Application.ScreenUpdating = False

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Set Rng = ActiveCell
Sheets("Weekly Valid").Select
Columns("C:AY").EntireColumn.Hidden = False
Range("D:E,G:H,J:K,M:N,P:Q,S:T,V:W,Y:Z,AB:AC,AE:AF,AH:AI,AK:AL,AN:AO,AQ:AR,AT:AU,AW:AX,AA:AD").EntireColumn.Hidden = True
Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12")).Select
Sheets("P1").Activate
Rows("6:283").EntireRow.Hidden = False
Range("29:32, 85:88,141:144,197:200,253:256").EntireRow.Hidden = True
Sheets("P1 Balance").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("P1 Balance").Select
Sheets(Array("P1 Balance", "P2 Balance", "P3 Balance", "P4 Balance", "P5 Balance", _
"P6 Balance", "P7 Balance", "P8 Balance", "P9 Balance", "P10 Balance", "P11 Balance" _
, "P12 Balance")).Select
Sheets("P1 Balance").Activate
Rows("5:23").EntireRow.Hidden = False
Rows("14:15").EntireRow.Hidden = True
Sheets("P12 Balance").Range("A1").Select
Application.ScreenUpdating = True
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect Password:=unpass
Next
End Sub