Custom View Macro for Excel 2007

[Winnipeg Michael]

I have a custom view, that I would like to turn into a macro, so I can click
a button, instead of having to click the Custom View button and subsequent
clicks.

Does anyone have one already made, or can share what I need to do to write
the macro?

Thank you

 

[Excel.Instructor]

I have a custom view, that I would like to turn into a macro, so I can click
a button, instead of having to click the Custom View button and subsequent
clicks.

Does anyone have one already made, or can share what I need to do to write
the macro?

Thank you

Winnipeg Michael:
Attached is the code you can use. Replace the "MyView" with the name
of your custom view.

ActiveWorkbook.CustomViews("MyView").Show

Best of luck.

Excel.Instructor (Ed2go.com/Advanced Excel)

 

[Winnipeg Michael]

Hey Excel.Instructor.

Thanks for your post.

It almost works.
I can run the macro in my Personal.xlsx sheet. When I try to use the macro
in another spreadsheet (with Personal.xlsx open), I get the "Run-time error
'5': Invalid procedure call or argument" error. Rats.

Because I actually want to use this macro on other spreadsheets, and not the
Personal.xlsx, does it mean that I cant use it? Or is it a simple error that
can be fixed?

Thanks

Michael

 

[RyanH]

You are getting the error because the macro is looking for the CustomView in
your new spreadsheet and it isn't there. The CustomView is saved in the
workbook it was created in.

My suggestion would be use the macro recorder to recorder how you manipulate
the worksheet view such as hidding rows, zooming, etc. and use that recording
as your custom view. For example, put the code below in a Personal Workbook
Module.

Sub CustomView()

ActiveWindow.Zoom = 75
With ActiveSheet
.Cells.EntireRow.Hidden = False
.Rows("1:5").EntireRow.Hidden = True
End With

End Sub

Hope this helps! If so, click "Yes" below.

 

[Winnipeg Michael]

Thanks RyanH for your reply.

I will try that and let you know.

Michael

 

[Winnipeg Michael]

Hi RyanH.

Took your example, tweaked it to what I was looking for:

Sub Macro5()
'
' Macro5 Macro
'

'
With ActiveSheet
.Columns("H:I").EntireColumn.Hidden = True
.Columns("U:AE").EntireColumn.Hidden = True
End With
End Sub

.........and it works like a charm.

Thanks so much.
If I need more help with macros, can I contact you?

Thanks again,
Michael