"Method 'Sheet' of object '_Global' failed" or "Object variable or with block variable not set"

[Patrick_GB]

When trying to use an excel file with macro develop and successfully tested on Excel 2007 from an Excel 2010 got this error message.

The issue is related to a sheets("Dashboard").Select = True crashing.

Below is the entire code entered in Excel:

THIS WORKBOOK
--------

Private Sub Workbook_Open()
'
' Initialise the application and hide Excel toolsbar, Formula Bar and Ribbon
' Access to the Dashboard sheet to start filling the spreadsheet
'
Application.ShowDevTools = False
Main_Page

End Sub
'-------------------------------------------------------------------------------------------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Reset Excel Toolsbar, Scrollbars, Formula Bar and Ribbon before closing
'
Application.ShowDevTools = True
Unhide

End Sub
'-------------------------------------------------------------------------------------------------------



MODULE 1
--------

Sub Main_Page()
'
' Display the Dashboard page
'
Dim Dashboard As Excel.Worksheet
Dim Rate_month As Excel.Worksheet

Application.MoveAfterReturnDirection = xlToRight
Initiate

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Initiate()
'
' Initiate the main page
'
Sheets("Dashboard").Visible = True
Sheets("Dashboard").Select
Sheets("Rate month").Visible = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.WindowState = xlMaximized
Hide_Dashboard
Range("A3").Select

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Hide_Dashboard()
'
' Hide the Formula Bar, the Woorkbook Tabs and display the Headings on the Dashboard page
'
Application.DisplayFormulaBar = False
ActiveWindow.WindowState = xlMaximized
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = False
.DisplayGridlines = False
.DisplayHeadings = True
End With

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Hide_Rate()
'
' Hide the Formula Bar, the Scroll Bars and the Woorkbook Tabs on the Rate month page
'
Application.DisplayFormulaBar = False

With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayGridlines = False
.DisplayHeadings = False
End With

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Unhide()
'
' Unhide the Formula Bar, the Scroll Bars and the Woorkbook Tabs
'
Application.ShowDevTools = True
Application.DisplayFormulaBar = True

With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayGridlines = True
.DisplayHeadings = True
End With

End Sub
'-------------------------------------------------------------------------------------------------------

Sub New_Line()
'
' Insert New Line Macro
'
Save_Workbook
UnProtect_DashboardSheet

Rows("3:3").Select
Selection.Copy
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3:R3").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A3").Select
Format_NewLine
Formula_Reinstatement
Range("A3").Select
Protect_DashboardSheet

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Save_Workbook()
'
' Save_Workbook Macro
'
ActiveWorkbook.Save
ChDir "\\192.168.0.4\Test_Folder\Tools from Tracy How"
ActiveWorkbook.SaveAs Filename:="\\192.168.0.4\Test_Folder\Tools from Tracy How\Pro Forma 2015.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
'-------------------------------------------------------------------------------------------------------

Sub New_Rate()
'
' Insert New Rate Macro
'
Sheets("Rate_month").Visible = True
Sheets("Rate_month").Select
Sheets("Dashboard").Visible = False
Hide_Rate
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Width = 310
.Height = 350
End With
Range("C4").Select

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Return_Dashboard()
'
' Return to Dashboard Macro
'
Sheets("Dashboard").Visible = True
Sheets("Rate_month").Visible = False
Sheets("Dashboard").Select
Hide_Dashboard
Range("A3").Select

End Sub
'-------------------------------------------------------------------------------------------------------

Sub Format_NewLine()
'
' Format the newly created line
'
Range("A3").Select
Selection.NumberFormat = "mm/yyyy"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("B3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("C3").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("D3").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("E3").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("F3").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("G3").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("H3").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("I3").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveWindow.SmallScroll ToRight:=1
Range("J3").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("K3").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("L3").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveWindow.SmallScroll ToRight:=3
Range("M3").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("N3").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveWindow.SmallScroll ToRight:=3
Range("O3").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("P3").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("Q3").Select
Selection.NumberFormat = "#,##0_ ;[Red]-#,##0 "
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("R3").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
End Sub
'-------------------------------------------------------------------------------------------------------

Sub Formula_Reinstatement()
'
' Macro1 Macro
'
Range("J4:M4").Select
Selection.Copy
Range("J3").Select
ActiveSheet.Paste
Range("J3").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("P4:R4").Select
Selection.Copy
Range("P3").Select
ActiveSheet.Paste
End Sub
'-------------------------------------------------------------------------------------------------------

Sub Protect_DashboardSheet()
'
' Macro1 Macro
'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End Sub
'-------------------------------------------------------------------------------------------------------

Sub UnProtect_DashboardSheet()
'
' Macro2 Macro
'
ActiveSheet.Unprotect
End Sub

I tried various options and none work where the simplified one was working on the Excel 2007.

Any idea from where the problem can come from and explaination about the difference between Excel 2007 and 2010?

Thank you for your answer