If Statement - Select Case - refer to specific cells

I currently have a large macro covering data on 31 sheets. The macro works currently as is but what happens requires a lot of user input and I would like to get rid of that.

The macro currently asks the user is there is data on sheet 1. If the user says yes, it copies and pastes the range (A13:P72) into sheet 32.
The macro asks this yes or no question for nearly every sheet. It's annoying to have to push yes 30 times....

I have no set up several =counta and =countif formulas on each sheet that I would like to use to automate this macro.

Would an if statement or a select case statement work better and how would I use either of them to check if a specific cell is great than 0.

ie: If (Sheet 'Item Page No. 1', Cell AA14 or AA15) > 0 Then
Copy and Past the page (I don't need the code for this)
If Else (move to the next sheet ('Item Page No. 2', Cell AA14 or AA15 (check if either cell is >0) .....

Any help with either the if statement or select case statement (I've never used select case) and how to refer to those specific cells and check whether they are greater than 0 or not would be very helpful.

as in...

Please Login or Register  to view this content.

?? I'm not sure on the syntax when referring to those values so please correct me if I'm wrong. Also, the result of both cases would be to copy and paste the same information. If both values are > 0, wouldn't they both apply and then result in copy and pasting the same information twice?

TMS, I don't think this would work.
For more background on this workbook, it is a template with a lot of information on each sheet filled with unit rates and such. As the user goes through the sheets, they can fill out only a select few cells (whether they apply or not). I have the counta function and countif functions checking those cells that they have the possibility of entering information into.

Also, after sheet 30, there are several sheets that summarize data but require no user input. so if the macro checked those sheets and applied a specific copy and paste function, unwanted data would be entered.

Thank you for the suggestion, though, I have never used 'For Each' before - I can see this code being a very useful reference later.

Any other thoughts?

Hi..

I am a bit late.. but here is what i got..

Please Login or Register  to view this content.

Norie,
AA14 has the following: =COUNTA(A13:B72) --- Cells in which the user would fill in job code data (column A) and description of item (Column B).
AA15 has the following: =COUNTIF(P13:P72,">0") --- Cells in which sum the total of the item costs.

If there are no items on the page, or no costs on the page, I want the page to be ignored.

I believe the code you provided would do the job, but would it be better to use this:

Please Login or Register  to view this content.

or this:

Please Login or Register  to view this content.

Definitely not sure on the syntax for the second option but I think you'll know what I mean and know how to fix it.
Any other recommendations?

apo,
Similar to TMS, I don't think this would for because I'm not going through every single sheet, just about 30 of them - then there are others I want to disregard.

But thank you for the suggestion.

The first 10 sheets are identical and I have placed a two count functions in AA14 and AA15. If either of those return a number other than 0 (ie: if there is anything written in important cells) The page would need to be copied and pasted.

The next 5 sheets are different than the first 10, but identical with each other. The count functions are in different cells, because there is other information in AA14 and AA15.

The next sheets ... and so on. So not all 30 sheets are identical is what I'm trying to say. The program as is asks the user, "is there any information on Item Page No. 1?" If they answer yes, it copies and pastes, if no, it moves to the next page.

As I mentioned in the original question, this can be annoying to the user as well as offers a large margin for user error (it's easy to forget what you've written on each page). I'm just trying to automate this.

Norie,
The last thing you posted might work wonders. I may have to add a few more cases for the pages that have different ranges to copy and whatnot but I think I can work with that.

Thank you very much all of you. Reputation Added.

I will mark this complete after I try it. I'll post here again if I'm still struggling.

Set a flag?

Norie - because each of you have recommended this, I am currently doing so.

I guess I just didn't think that the code if statement or select case statement could be so generic and still apply. I figured I would be writing the same code for each individual sheet minus the few discrepancies. I was wrong.

Thanks again.

TMS,
Thank you for explaining. Very much appreciated.