Hiding rows based on a certain cell's value which is a formula

[jbh040]

It's been years since I have did anything with VBA and macros and cannot figure out what I am doing wrong. I have a workbook that has multiple worksheets including a Cover Sheet, Template Sheet, and Location Sheet, as well as others. I have created specific templates on the template sheet and location sheet which pull data from the cover sheet. I have programmed the template sheet so that the sheets are blank until I generate data on the cover sheet from a data sheet at which point it populates the data in the template sheet. When I populate data and print the template sheet, it prints all 33 sheets even though there may only be 1 or 2 pages that actually display values. I have formulas that return data on the template sheet in columns A-F. Column B is my reference cell column that I am trying to use to hide or unhide the rest of the rows. The code that I attempted to create and use is below. I want it to look at cell B21 and if B21 is blank as derived from a formula ='Cover Sheet"!$A$9 due to A9 on the Cover Sheet being blank, I want rows 1-34 to be hidden and so on for the other rows. I have tried if then statements in my macros but no luck either. I know that I can use print selection to achieve what I am after but I am going to create a print button to make it easier for other users of this spreadsheet.

Please Login or Register  to view this content.

[xladept]

Try this instead:

Please Login or Register  to view this content.

[jbh040]

Thank you for the reply. I used this code but was unsuccessful. I am getting the RunTime Error '1004': Unable to set the Hidden property of the range class. I had tried the if then statements before and got this error.

[jbh040]

I figured out the issue. Because I had the sheet protected, it was not allowing the macro to run. Can anyone tell me the code to have that macro unprotect the sheet and then run the macro and then protect the sheet again.

[xladept]

Maybe:

Please Login or Register  to view this content.

*If there is no password then just delete that part.

And - Thanks for the rep!