Cannot jump to worksheet because it is hidden - vba headache

blzbub

New Member
Joined
Oct 1, 2009
Messages
24
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
See if this fixes the visible issue:
Code:
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

New Member
Joined
Oct 1, 2009
Messages
24
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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

New Member
Joined
Oct 1, 2009
Messages
24

ADVERTISEMENT

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

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
My bad, change that line to:
Code:
x = .Cells(.Rows.Count, 5).End(xlUp).Row
 

blzbub

New Member
Joined
Oct 1, 2009
Messages
24
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top