Copy a sheet with hidden rows and past to another sheet keeping formula at some cells

[AndyVoClosser]

Dear community,

I have encountered a problem to copy a sheet with hidden rows keeping the formula at some cells. I describe my problem as follows: I have a worksheet with the range "A1:AJ1500". At the row 1500, there are some cells to calculate the sum function for some columns. For example, cell B1500 = sum(B1:B1400) and cell H1500 = count(H1:H1400, X)... so on.

Now, I want to hide some rows, for instance Rows A100:AJ150 are hidden. Then copy the visible cells to an another sheet keeping the formulas at the row 1500. I have tried the "move or copy" sheet method. It works but it does not what I want. I want to have a copy button in my sheet with a macro. Thus, the users can copy the sheet to pass to their workbook for using. I appreciate any comments for solving my problem. Thank you so much.
Andy

 

[Candyman8019]

You can try changing the formulas to subtotal. For example: =subtotal(109,B1:B1400)
This will ignore any hidden rows in the sum.