Cannot jump to worksheet because it is hidden - vba headache

[blzbub]

Hi all, I am trying to get the basics for a new project collating data from various sheets and loading it into the last sheet in a workbook. I have a workbook with 5 sheets named Alpha, Bravo, Charlie, Delta and Echo. I am using checkboxes in Delta to select all or a combination of the various preceding sheets. A command button then copies through data onto Echo depending on which checkboxes are ticked. My code works if static ranges are used for the target sheets, i.e. range("A1") or range("A3:G18") but I want to create dynamic ranges for varying data sets, i.e. range("A1", range("F100").End(xlup)) etc....
When I change the code to include dynamic ranges I get an error "Cannot jump to worksheet because it is hidden" .... well it isn't !!!, no sheets are hidden they are 'out of the box' worksheets with some text in various cells and this is driving me mad, spent far too long at work today mucking about with this (better than real work though). My code is below, any thoughts would be appreciated, I am using Excel 2007,

Oh I have searched threads for similar but to no avail -

Thanks,

(on sheet "Echo", the 4th of 5 in workbook)
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Worksheets("Echo").Range("a2:e500").ClearContents
If CheckBox1.Value = True Then Worksheets("Alpha").Range("A1:e5").Copy _
Destination:=Worksheets("Echo").Range("a100").End(xlUp).Offset(1, 0)
If CheckBox2.Value = True Then Worksheets("Bravo").Range("A1:e5").Copy _
Destination:=Worksheets("Echo").Range("a100").End(xlUp).Offset(1, 0)
If CheckBox3.Value = True Then Worksheets("Charlie").Range("A1:e5").Copy _
Destination:=Worksheets("Echo").Range("a100").End(xlUp).Offset(1, 0)
Range("a1").Select
Application.ScreenUpdating = True
MsgBox "All Done"
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
End Sub

 

[JackDanIce]

See if this fixes the visible issue:

Private Sub CommandButton1_Click()

    Dim ws  As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    
    With Sheets("Echo")
            
        If ActiveSheet.Name <> .Name Then .Select
            
        .Range("A2:E500").ClearContents
        
        If Checkbox1.value Then
            Sheets("Alpha").Range("A1:E5").Copy
            .Range("A100").End(xlUp).Offset(1).PasteSpecial xlPasteAll
        End If
        
        If CheckBox2.value Then
            Sheets("Bravo").Range("A1:E5").Copy
            .Range("A100").End(xlUp).Offset(1).PasteSpecial xlPasteAll
        End If
        
        If Checkbox3.value Then
            Sheets("Charlie").Range("A1:E5").Copy
            .Range("A100").End(xlUp).Offset(1).PasteSpecial xlPasteAll
        End If

    End With
    
    Checkbox1.value = False
    CheckBox2.value = False
    Checkbox3.value = False
    
    Application.ScreenUpdating = True
    
    MsgBox "All Done"
    
End Sub

 

[blzbub]

Thanks (love your username!) but if I replace code line under If checkbox1.value = true with Sheets("Alpha").Range("A1", Range("E100").End(xlUp)).Copy it errors again. Same message -

Run-time error'1004' Application defined or object defined error and when I debug I get Cannot jump to 'sheets' because it is hidden.

The sheets were never hidden, all visible and I have no plan to hide any. I am just wondering if 'hidden' in this scenario means something else?

I will keep trying but I am sure there is a simple reason/fix.

Cheers,

 

[JackDanIce]

I'm fond of a bit of JD with ice, thanks for the compliment!

I think I can see how your attempts are erroring. Just trying running this (do not change any of the code) and confirm if it works, then we can worry about the dynamic part:

Private Sub CommandButton1_Click()

    Dim ws  As Worksheet
    Dim x   As Long
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    
    With Sheets("Echo")
            
        If ActiveSheet.Name <> .Name Then .Select
            
         x = .Cells(5, .rows.Count).End(xlUp).row
        .Range("A2:E" & x).ClearContents
        
        If Checkbox1.value Then
            x = Sheets("Alpha").Cells(5, rows.Count).End(xlUp).row
            Sheets("Alpha").Range("A1:E" & x).Copy
            .Range("A" & .rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
        End If
        
        If CheckBox2.value Then
            x = Sheets("Bravo").Cells(5, rows.Count).End(xlUp).row
            Sheets("Bravo").Range("A1:E" & x).Copy
            .Range("A" & .rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
        End If
        
        If Checkbox3.value Then
            x = Sheets("Charlie").Cells(5, rows.Count).End(xlUp).row
            Sheets("Charlie").Range("A1:E" & x).Copy
            .Range("A" & .rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
        End If

    End With
    
    Checkbox1.value = False
    CheckBox2.value = False
    Checkbox3.value = False
    
    Application.ScreenUpdating = True
    
    MsgBox "All Done"
    
End Sub

 

[blzbub]

Hi JD, thanks for this, unfortunately it fails at the line -

x = .Cells(5, .Rows.Count).End(xlUp).Row

Any ideas?

JD has always been my tipple - when I was younger you had Jack Daniels drinkers and then the Southern Comfort brigade (pah)

 

[JackDanIce]

My bad, change that line to:

x = .Cells(.Rows.Count, 5).End(xlUp).Row

 

[blzbub]

I changed it throughout the code and it will work but only if there is data in E5, however I can workaround that - I owe you a large JD I think.

Your time and effort is very much appreciated.

 

[JackDanIce]

You're welcome and I'll take you up on that JD if I'm ever in Newcastle/you in London!

Best of luck with rest of the coding.