If and ELseIF

[imback2nite]

I've written this and don't know what is going wrong.

Private Sub CommandButton3_Click()
        If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")
            Exit Sub
       ElseIf Range("4:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues
        ElseIf Range("51:54").EntireRow.Hidden = False Then
            Range("B56:B95").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B56").PasteSpecial Paste:=xlPasteValues
       ElseIf Range("99:102").EntireRow.Hidden = False Then
            Range("B104:B143").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B104").PasteSpecial Paste:=xlPasteValues
       ElseIf Range("147:150").EntireRow.Hidden = False Then
            Range("B152:B191").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B152").PasteSpecial Paste:=xlPasteValues
        End If
            Application.CutCopyMode = False
        Range("B1").Select
    Application.ScreenUpdating = True
End Sub

It all works except for the

       ElseIf Range("4:6").EntireRow.Hidden = False Then
            Range("B8:B47").ClearContents
            Worksheets("Names").Range("B2:B41").Copy
            Range("B8").PasteSpecial Paste:=xlPasteValues

The rest of the ElseIf's work fine. I tried deleting the code below and it works fine but I do need it. Any help wound be appreciated.

 If Range("3:2544").EntireRow.Hidden = False Then
        MsgBox ("Select Which Week To Update Names")
            Exit Sub

[/CODE] Because the code works fine without it.

 

[Alex Blakenburg]

What hides or unhides each section ? Show us the code and give a short description of the logic.

What are you trying to do with the CommandButton3 code ? Are you just trying to find the unhidden section ?

This should do the same thing as what you are trying to do with your code:

Private Sub CommandButton3_Click()
    Dim ws As Worksheet
    Dim iHdgRows As Long, iBodyRows As Long
    Dim iStartRow As Long
    Dim LastRow As Long, iSectRows As Long
    Dim VisibleSectFound As Boolean
    Dim i As Long

    Set ws = Worksheets("TOKES")
    LastRow = Range("B" & Rows.Count).End(xlUp).Row + 3

    iStartRow = 3
    iHdgRows = 5
    iBodyRows = 40
    iGapRows = 3
    iSectRows = iHdgRows + iBodyRows + iGapRows
    
    VisibleSectFound = False
 
    For i = iStartRow To LastRow Step iSectRows
        With ws
            If .Cells(i, "B").EntireRow.Hidden = False Then
                .Cells(i, "B").Offset(iHdgRows).Resize(iBodyRows).ClearContents
                Worksheets("Names").Range("B2:B41").Copy
                .Cells(i, "B").Offset(iHdgRows).PasteSpecial Paste:=xlPasteValues
                
                VisibleSectFound = True
                Exit For

            End If
        End With
    Next i
    
    If VisibleSectFound = False Then
        MsgBox ("Select Which Week To Update Names")
        Exit Sub
    End If

End Sub

 

[imback2nite]

What hides or unhides each section ? Show us the code and give a short description of the logic.

What are you trying to do with the CommandButton3 code ? Are you just trying to find the unhidden section ?

This should do the same thing as what you are trying to do with your code:

Private Sub CommandButton3_Click()
    Dim ws As Worksheet
    Dim iHdgRows As Long, iBodyRows As Long
    Dim iStartRow As Long
    Dim LastRow As Long, iSectRows As Long
    Dim VisibleSectFound As Boolean
    Dim i As Long

    Set ws = Worksheets("TOKES")
    LastRow = Range("B" & Rows.Count).End(xlUp).Row + 3

    iStartRow = 3
    iHdgRows = 5
    iBodyRows = 40
    iGapRows = 3
    iSectRows = iHdgRows + iBodyRows + iGapRows
   
    VisibleSectFound = False
 
    For i = iStartRow To LastRow Step iSectRows
        With ws
            If .Cells(i, "B").EntireRow.Hidden = False Then
                .Cells(i, "B").Offset(iHdgRows).Resize(iBodyRows).ClearContents
                Worksheets("Names").Range("B2:B41").Copy
                .Cells(i, "B").Offset(iHdgRows).PasteSpecial Paste:=xlPasteValues
               
                VisibleSectFound = True
                Exit For

            End If
        End With
    Next i
   
    If VisibleSectFound = False Then
        MsgBox ("Select Which Week To Update Names")
        Exit Sub
    End If

End Sub

Wow! Now that is nice! Elegant. I use the 'Dates' button to bring up the 'Month(s)' userform. Then the dated buttons to select the appropriate week. I actually have 52 weeks populating the 'TOKES' page, vertically. The 'Dated' buttons unhide that week while hiding the rest. Then the user can input the pertinent information. Command Button 3 or 'Input Names' will see the 'open area and input the 'Names', which are shown as numbers for illustration. What I have is a 'Show All' button that will show all 52 weeks. But when all 52 weeks are shown I don't want the user to be able to populate the 'TOKES' page till a week is chosen. That is why I needed the MsgBox alerting then user.

 

[Alex Blakenburg]

Thanks for elaborating. Assuming we have solved the topic of this thread I will leave it there on this one.

 

[imback2nite]

Thanks for elaborating. Assuming we have solved the topic of this thread I will leave it there on this one.

Thank you! I really couldn't have done it without your help. I really need to learn the correct way of writing. My way is so juvenile. Thank you once again!