If Statement - Select Case - refer to specific cells

**ahilty** · 07-10-2013, 08:37 AM

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.

**Norie** · 07-10-2013, 09:02 AM

I don't see how a Select Case would work, just use an If statement within a loop.

**ahilty** · 07-10-2013, 09:07 AM

as in...

If Range("AA14").Value > 0 Then
'bla bla bla

Else If Range("AA15").Value > 0 Then
'bla bla bla

?? 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** · 07-10-2013, 09:08 AM

Maybe:

Sub sCheckData()

Dim sh As Worksheet

For Each sh In Worksheets
    If sh.UsedRange.Cells.Count > 1 Then
        ' usedrange.cells.count will = 1 if sheet empty
        ' do something with the sheet (sh) here ...
        MsgBox sh.Name & " " & sh.UsedRange.Cells.Count
    End If
Next 'sh
End Sub

Regards, TMS

**ahilty** · 07-10-2013, 09:14 AM

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?

**apo** · 07-10-2013, 09:19 AM

Hi..

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

Private Sub CommandButton1_Click()
Dim wsh As Worksheet
For Each wsh In Worksheets
    If wsh.Cells(14, 1).Value > 1 Or wsh.Cells(15, 1).Value > 1 Then
        MsgBox "Copy and Paste " & wsh.Name ' Insert your actual code for copy and paste instead
    End If
Next wsh
End Sub

**ahilty** · 07-10-2013, 09:24 AM

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.

**Norie** · 07-10-2013, 09:10 AM

It would only result in copying twice if you repeated the code.

If your criteria is to copy if either cell value is > 0 then use Or.

If Range("AA14").Value > 0 Or Range("AA15").Value > 0 Then

      ' do the copy
End If

By the way, make sure you use worksheet references in the code when referrring to ranges, otherwise you might end up with some unexpected results.

PS What's in AA14/AA15?

**ahilty** · 07-10-2013, 09:21 AM

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:

Sheets("Item Page No. 1").Activate
If Range("AA14").Value > 0 Or Range("AA15").Value > 0 Then

      ' do the copy
End If

Sheets("Item Page No. 2").Activate
If Range("AA14").Value > 0 Or Range("AA15").Value > 0 Then

      ' do the copy
End If

' So on and so forth....

or this:

If Sheets("Item Page No.1").Range("AA14").Value > 0 Or Range("AA15").Value > 0 Then

      ' do the copy
End If

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** · 07-10-2013, 09:30 AM

HI....

So you want to ignore (move to next sheet) if cells 14,1 and 15,1 are blank?

If so.. this should work..? basically.. its saying that if Cells 14,1 or 15,1 are blank then move to the next sheet... or am i missing something simple?

Private Sub CommandButton1_Click()
Dim wsh As Worksheet
For Each wsh In Worksheets
    If wsh.Cells(14, 1).Value <> "" Or wsh.Cells(15, 1).Value <> "" Then
        MsgBox "Copy and Paste " & wsh.Name ' Insert your actual code for copy and paste instead
    End If
Next wsh
End Sub

**ahilty** · 07-10-2013, 09:36 AM

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** · 07-10-2013, 09:33 AM

Sorry I'm confused.

Are you seriously going to repeat the same code for all the sheets?

The suggestion I made would have the If statement within a loop.

That loop would be going through the sheets you want to, possibly, copy from.

Dim ws As Worksheet
Dim wsDst As Worksheet

    Set wsDst = Worksheets("Sheet32")

    For Each ws In ThisWorkbook.Worksheets

        Select Case ws.Name
            Case "Sheet32", <list of sheet names not to copy from>
                ' do nothing
            Case Else
                ' for all the sheets you want to copy from
                If ws.Range("AA14").Value > 0 Or ws.Range("AA15").Value > 0 Then
                    ' do the copy
                    ws.Range("A13:P72").Copy wsDst.Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
        End Select
    Next ws

**TMS** · 07-10-2013, 09:45 AM

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.

Maybe you could have some (hidden) helper cells in the same position on the first 15 sheets linking to the relevant counters ... in fact, you'd only need one that checked the values of both counters and set a flag.

Regards, TMS

**ahilty** · 07-10-2013, 09:47 AM

Set a flag?

**Norie** · 07-10-2013, 09:40 AM

You never mentioned that the cells to look in where different for different sheets.

If that's the case then we could expand the Select Case so that different cells are checked for different worksheets.

To do that we would need more information, eg sheet names.

**ahilty** · 07-10-2013, 09:41 AM

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.

**TMS** · 07-10-2013, 09:42 AM

Loop through a designated list (array) of sheet names.

Sub sCheckData2()

Dim sh As Worksheet

For Each sh In Sheets(Array("Sheet1", "Sheet3"))
    If sh.UsedRange.Cells.Count > 1 Then
        ' usedrange.cells.count will = 1 if sheet empty
        ' do something with the sheet (sh) here ...
        MsgBox sh.Name & " " & sh.UsedRange.Cells.Count
    End If
Next 'sh
End Sub

Change the array to list the sheets you are interested in (and you could pull it from a sheet if you wanted)

Change the If to check the cells AA14 and AA15 as in the other examples.

Regards

TMS

**Norie** · 07-10-2013, 09:53 AM

Just curious, why don't you put the formulas that determine whether a sheet is to be copied from in the same cell on each worksheet?

**TMS** · 07-10-2013, 09:57 AM

Flag: a value to check. If the flag is set, copy the workbook, if it's not, don't.

Helper Cell (flag): =IF(OR(AA14>0, AA15>0), 1, "") ... same position on every sheet but it would refer to the relevant cells on that sheet. You could also use the same helper cell on the other sheets you do or do not want by simply putting a 1 or blank in it.

Let's say, you used cell AZ1 as the flag on every sheet and either put a formula in it or a 1 for those sheets you always want. The value of the formula would determine if the sheet got copied.

Sub sCheckData2()

Dim sh As Worksheet

For Each sh In Sheets(Array("Sheet1", "Sheet3"))
    If sh.Range.("AZ1") = 1 Then
        ' do something with the sheet (sh) here ...
        MsgBox sh.Name & " to be copied"
    End If
Next 'sh
End Sub

Only copy sheets with a 1 in the cell.

Anyway, an idea. Saves complicated code which is different for different (groups of ) sheets.

Regards, TMS

**ahilty** · 07-10-2013, 09:57 AM

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.

**ahilty** · 07-10-2013, 09:58 AM

TMS,
Thank you for explaining. Very much appreciated.

If Statement - Select Case - refer to specific cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Re: If Statement - Select Case - refer to specific cells

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions