Automate Worksheet Views — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Automate Worksheet Views

Get PDF file

by on
in The Office Tech Pro

If you have a workbook that everyone views a little bit differently, you can automate it, so that everyone just has to click their own button. Even if you are the only one who uses it, but you use it differently for different purposes, this solutionMicrosoft Excelwould work, too.

First, if you’re not already familiar with the Custom View tool, it allows you to save filtering and hidden rows or columns. You can also save print settings, enabling you to have multiple print settings on the same worksheet for different purposes.

Once you have hidden rows and columns and filtered the remaining ones to suit your needs, on the View tab, in the Workbook Views group, click the Custom Views button.Microsoft Excel

Click Add to create your first custom view. Name it something relative to its use to make it easy to refer to again. You can choose to save only hidden rows, columns and filter settings or only print settings by checking/unchecking the boxes.

Once you’ve created custom views for all your uses or users, you will record aMicrosoft Excel macro that simply navigates to the view. Decide now whether you will put buttons on that worksheet, buttons on another worksheet, or just assign shortcuts. If you choose to assign shortcuts, it would be good to include a worksheet that serves as a legend to those.

To record your macro, begin on the Home tab with your cursor at the top left in A1 (or top leftmost visible cell). On the View tab, click the Macros button and choose Record Macro. Name it the same as the custom view, but eliminate any spaces or special characters except _ . Assign a shortcut if desired and click OK. Click the View tab, the Custom Views button and select the custom view to activate this macro. Then, stop recording. Now you can either assign this macro to button drawing objects or include that shortcut on your legend worksheet tab.

Leave a Comment

Previous post:

Next post: