Delete/clear sheet macro not working

Hi all,

I have a dashboard with a huge amount of sheets, with quite a bit of info, making it impossible to e-mail it, and the HTML published document is too limited as well.

To address this, I have tried this macro, but it produces an error message: Error: Compile error in hidden module: Module 2

When testing it in Excel itself, it works like a charm, but no luck when running the report.

Sub ClearAllSheets()

'

' DeleteSheets Macro

' Macro recorded 2010/07/26 by Herman van Noordwyk

'

'   Copy and paste special as values

'   Select the only sheet not to be deleted, Sheet2 also to remain, can be hidden

    Application.DisplayAlerts = False

    Sheets("Dashboard").Activate

    Cells.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("Dashboard").Select

'   Clear Sheets

    Sheets(Array("Sheet1", "Transactions", "Date_Lup", "Lookup", "TB_Alloc",
"StkTurn", "Sales", "InvVlookup",
"InvSum", "InvAlloc", "Inv_CN",
"DebtorsMaster", "Cashflow", "BalSht",
"Consol")).Select

    Selection.ClearContents

'   Hide all sheets (in HideAllSheets macro for troubleshooting)

'    Sheets(Array("Sheet1", "Transactions", "Date_Lup", "GLCat", "Categories",
"Lookup", "Ledger Transaction Details",
"TB_Alloc", "Instructions", "StkTurn",
"Sales", "InvVlookup", "InvSum",
"InvAlloc", "Inv_CN", "DebtorsMaster",
"Cashflow", "BalSht", "Consol")).Visible = False

    Application.DisplayAlerts = True