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
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