Excel VBA sheet hiding script

Hi, I have been trying to write a do loop that goes through all my worksheets and either sets the print area of a worksheet or hides the worksheet from printing.

The script starts with an active cell on the coversheet and checks the number in it. If the number is 1-4 it will set a specific print area for a worksheet then increment on to the next cell. If the number is 0 it hides the sheet and moves on to the next cell. Each cell is should determine which sheets are hidden and which are not, kind of like going through a table of contents and hiding pages with a 0 next to them.

My difficulty is when i reference to a list of the worksheet names so that the script knows which sheet i want hidden. Can i somehow increment through the sheets until i reach a specific one, or is there just a slight language error in my script?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:

Do not select your sheets before setting the print area as it will slow down your code a lot

To loop through all your sheets why not usie something like the attached code

Sub SetSheets()
   Dim s As Worksheet
   Sheets("Macros Disabled").Visible = True
   For Each s In Sheets
      Select Case s.name
         Case "Sheet1"
            s.PageSetup.PrintArea = "$A$1:$R$252"
         Case "Sheet2"
            s.PageSetup.PrintArea = "$A$1:$R$189
         Case "Sheet3"
            s.PageSetup.PrintArea = "$A$1:$R$126"
         Case else
            s.Visible = xlSheetVeryHidden
      end select
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WTC_ServicesAuthor Commented:

I'm not sure this code does exactly what i want to do. My embedded example shows what I mean by a table of contents. I need the script to read through the Number of Pages column and perform the action listed in my Action column.


thanks, wtc
WTC_ServicesAuthor Commented:

I now have my script working nicely however am having difficulty with calling another function I have defined. The function should take as an input a value of an array of strings. This input will point it toward a sheet which it tests. Could you please help direct me with why this will not work?

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.