Macro does not work when I copy the worksheet, error 400

Question

Every month I have to create a new dashboard that I show "Open" for me to enter the information, and "Closed" so the managers see the totals only. I created the following macro in my sheet "September", but when I use the "Move or Copy" to create the sheet for October (this is within the same file), the macro does not work in the new sheet. This is a basic macro to hide and unhide rows and columns. (error 400) Please help;

Sub ShowClosed()
'
' ShowClosed Macro
'

'
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("B:J").Select
    Selection.EntireColumn.Hidden = True
    Rows("4:10").Select
    Selection.EntireRow.Hidden = True
    Rows("12:19").Select
    Selection.EntireRow.Hidden = True
    Rows("21:28").Select
    Selection.EntireRow.Hidden = True
    Rows("30:37").Select
    Selection.EntireRow.Hidden = True
    Rows("39:46").Select
    Selection.EntireRow.Hidden = True
    Rows("48:48").Select
    Selection.EntireRow.Hidden = True
End Sub
Sub ShowOpen()
'
' ShowOpen Macro
'

'
    Columns("A:O").Select
    Selection.EntireColumn.Hidden = False
    Rows("2:53").Select
    Selection.EntireRow.Hidden = False
    Range("F10").Select
End Sub

How are you calling the macro? New button in each sheet linked to same macro? — Automate This, Sep 20 '13 at 16:11
Also [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Sep 20 '13 at 16:33

score 0 · Answer 1 · answered Sep 20 '13 at 17:07

Sub ShowClosed()


Columns("B:J").EntireColumn.Hidden = True
Rows("4:10").EntireRow.Hidden = True
Rows("12:19").EntireRow.Hidden = True
Rows("21:28").EntireRow.Hidden = True
Rows("30:37").EntireRow.Hidden = True
Rows("39:46").EntireRow.Hidden = True
Rows("48:48").EntireRow.Hidden = True


End Sub
Sub ShowOpen()
'
' ShowOpen Macro
'

Columns("A:O").EntireColumn.Hidden = False
Rows("2:53").EntireRow.Hidden = False

End Sub

As Siddharth always preaches, stay away from select. Recording macro is always good for that. Also, put in a module rather than the sheet if you want it to work on different sheets.

Macro does not work when I copy the worksheet, error 400

1 Answers1