Code results are inconsistent

C

Catyclaire85

New Member
Joined
Nov 23, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
I have built code for shifts to be created and then uploaded into a different system. Some people work split shifts and others don’t. To make it easier to plan there are options to copy repeats using command buttons. The code I have written works as desired when I run it manually (F5 in VBA), however when run in testing only part of the code works as desired.

Attached are some screenshots and the code for this action.

VBA Code: 
Sub ShiftView()


Application.ScreenUpdating = False
  
    If Sheet1.Range("D33") = True Then 'split shifts
    Sheet2.Range("B6") = "Monday Split 1"
    Sheet2.Range("B9") = "Tuesday Split 1"
    Sheet2.Range("B12") = "Wednesday Split 1"
    Sheet2.Range("B15") = "Thursday Split 1"
    Sheet2.Range("B18") = "Friday Split 1"
    Sheet2.Range("B21") = "Saturday Split 1"
    Sheet2.Range("B24") = "Sunday Split 1"
   
    '1 wk rotation
        If Sheet1.ComboBox5 = "1" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
       
        Sheet2.Columns("G:Z").EntireColumn.Hidden = True
       
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = True 'copy Mon 2
        Sheet2.CommandButton3.Visible = True 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = True 'copy Tue 2
        Sheet2.CommandButton6.Visible = True 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = True 'copy Wed 2
        Sheet2.CommandButton9.Visible = True 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = True 'copy Thu 2
        Sheet2.CommandButton12.Visible = True 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = True 'copy Fri 2
        Sheet2.CommandButton15.Visible = True 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = True 'copy Sat 2
        Sheet2.CommandButton18.Visible = True 'copy Sat 3
       
        Sheet2.Rows("7:8").EntireRow.Hidden = False
        Sheet2.Rows("10:11").EntireRow.Hidden = False
        Sheet2.Rows("13:14").EntireRow.Hidden = False
        Sheet2.Rows("16:17").EntireRow.Hidden = False
        Sheet2.Rows("19:20").EntireRow.Hidden = False
        Sheet2.Rows("22:23").EntireRow.Hidden = False
        Sheet2.Rows("25:26").EntireRow.Hidden = False
       
    '2 wk rotation
        Else
        If Sheet1.ComboBox5 = "2" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:J").EntireColumn.Hidden = False
        Sheet2.Columns("K:Z").EntireColumn.Hidden = True
               
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = True 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
               
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = True 'copy Mon 2
        Sheet2.CommandButton3.Visible = True 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = True 'copy Tue 2
        Sheet2.CommandButton6.Visible = True 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = True 'copy Wed 2
        Sheet2.CommandButton9.Visible = True 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = True 'copy Thu 2
        Sheet2.CommandButton12.Visible = True 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = True 'copy Fri 2
        Sheet2.CommandButton15.Visible = True 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = True 'copy Sat 2
        Sheet2.CommandButton18.Visible = True 'copy Sat 3
       
        Sheet2.Rows("7:8").EntireRow.Hidden = False
        Sheet2.Rows("10:11").EntireRow.Hidden = False
        Sheet2.Rows("13:14").EntireRow.Hidden = False
        Sheet2.Rows("16:17").EntireRow.Hidden = False
        Sheet2.Rows("19:20").EntireRow.Hidden = False
        Sheet2.Rows("22:23").EntireRow.Hidden = False
        Sheet2.Rows("25:26").EntireRow.Hidden = False
       
    '3 wk rotation
        Else
        If Sheet1.ComboBox5 = "3" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:Z").EntireColumn.Hidden = False
        Sheet2.Columns("O:Z").EntireColumn.Hidden = True
               
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = True 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = True 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = True 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
               
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = True 'copy Mon 2
        Sheet2.CommandButton3.Visible = True 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = True 'copy Tue 2
        Sheet2.CommandButton6.Visible = True 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = True 'copy Wed 2
        Sheet2.CommandButton9.Visible = True 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = True 'copy Thu 2
        Sheet2.CommandButton12.Visible = True 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = True 'copy Fri 2
        Sheet2.CommandButton15.Visible = True 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = True 'copy Sat 2
        Sheet2.CommandButton18.Visible = True 'copy Sat 3
        Sheet2.Rows("7:8").EntireRow.Hidden = False
        Sheet2.Rows("10:11").EntireRow.Hidden = False
        Sheet2.Rows("13:14").EntireRow.Hidden = False
        Sheet2.Rows("16:17").EntireRow.Hidden = False
        Sheet2.Rows("19:20").EntireRow.Hidden = False
        Sheet2.Rows("22:23").EntireRow.Hidden = False
        Sheet2.Rows("25:26").EntireRow.Hidden = False
       
       
    '4 wk rotation
        Else
        If Sheet1.ComboBox5 = "4" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:R").EntireColumn.Hidden = False
        Sheet2.Columns("S:Z").EntireColumn.Hidden = True
               
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = True 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = True 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = True 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = True 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = True 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = True 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
               
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = True 'copy Mon 2
        Sheet2.CommandButton3.Visible = True 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = True 'copy Tue 2
        Sheet2.CommandButton6.Visible = True 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = True 'copy Wed 2
        Sheet2.CommandButton9.Visible = True 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = True 'copy Thu 2
        Sheet2.CommandButton12.Visible = True 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = True 'copy Fri 2
        Sheet2.CommandButton15.Visible = True 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = True 'copy Sat 2
        Sheet2.CommandButton18.Visible = True 'copy Sat 3
        Sheet2.Rows("7:8").EntireRow.Hidden = False
        Sheet2.Rows("10:11").EntireRow.Hidden = False
        Sheet2.Rows("13:14").EntireRow.Hidden = False
        Sheet2.Rows("16:17").EntireRow.Hidden = False
        Sheet2.Rows("19:20").EntireRow.Hidden = False
        Sheet2.Rows("22:23").EntireRow.Hidden = False
        Sheet2.Rows("25:26").EntireRow.Hidden = False
       
    '5 wk rotation
        Else
        If Sheet1.ComboBox5 = "5" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:V").EntireColumn.Hidden = False
        Sheet2.Columns("W:Z").EntireColumn.Hidden = True
               
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = True 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = True 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = True 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = True 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = True 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = True 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = True 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = True 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = True 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = True 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
               
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = True 'copy Mon 2
        Sheet2.CommandButton3.Visible = True 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = True 'copy Tue 2
        Sheet2.CommandButton6.Visible = True 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = True 'copy Wed 2
        Sheet2.CommandButton9.Visible = True 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = True 'copy Thu 2
        Sheet2.CommandButton12.Visible = True 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = True 'copy Fri 2
        Sheet2.CommandButton15.Visible = True 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = True 'copy Sat 2
        Sheet2.CommandButton18.Visible = True 'copy Sat 3


        Sheet2.Rows("7:8").EntireRow.Hidden = False
        Sheet2.Rows("10:11").EntireRow.Hidden = False
        Sheet2.Rows("13:14").EntireRow.Hidden = False
        Sheet2.Rows("16:17").EntireRow.Hidden = False
        Sheet2.Rows("19:20").EntireRow.Hidden = False
        Sheet2.Rows("22:23").EntireRow.Hidden = False
        Sheet2.Rows("25:26").EntireRow.Hidden = False
       
    '6 wk rotation
        Else
        If Sheet1.ComboBox5 = "6" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:Z").EntireColumn.Hidden = False
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = True 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = True 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = True 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = True 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = True 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = True 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = True 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = True 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = True 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = True 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
               
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = True 'copy Mon 2
        Sheet2.CommandButton3.Visible = True 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = True 'copy Tue 2
        Sheet2.CommandButton6.Visible = True 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = True 'copy Wed 2
        Sheet2.CommandButton9.Visible = True 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = True 'copy Thu 2
        Sheet2.CommandButton12.Visible = True 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = True 'copy Fri 2
        Sheet2.CommandButton15.Visible = True 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = True 'copy Sat 2
        Sheet2.CommandButton18.Visible = True 'copy Sat 3


        Sheet2.Rows("7:8").EntireRow.Hidden = False
        Sheet2.Rows("10:11").EntireRow.Hidden = False
        Sheet2.Rows("13:14").EntireRow.Hidden = False
        Sheet2.Rows("16:17").EntireRow.Hidden = False
        Sheet2.Rows("19:20").EntireRow.Hidden = False
        Sheet2.Rows("22:23").EntireRow.Hidden = False
        Sheet2.Rows("25:26").EntireRow.Hidden = False
       
        End If
        End If
        End If
        End If
        End If
        End If


    Else
    'None split shifts
    If Sheet1.Range("E33") = True Then
    Sheet2.Range("B6") = "Monday"
    Sheet2.Range("B9") = "Tuesday"
    Sheet2.Range("B12") = "Wednesday"
    Sheet2.Range("B15") = "Thursday"
    Sheet2.Range("B18") = "Friday"
    Sheet2.Range("B21") = "Saturday"
    Sheet2.Range("B24") = "Sunday"
    Sheet2.Rows("7:8").EntireRow.Hidden = True
    Sheet2.Rows("10:11").EntireRow.Hidden = True
    Sheet2.Rows("13:14").EntireRow.Hidden = True
    Sheet2.Rows("16:17").EntireRow.Hidden = True
    Sheet2.Rows("19:20").EntireRow.Hidden = True
    Sheet2.Rows("22:23").EntireRow.Hidden = True
    Sheet2.Rows("25:26").EntireRow.Hidden = True


        '1 week rotation
        If Sheet1.ComboBox5 = "1" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = False 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
                     
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = False 'copy Mon 2
        Sheet2.CommandButton3.Visible = False 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = False 'copy Tue 2
        Sheet2.CommandButton6.Visible = False 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = False 'copy Wed 2
        Sheet2.CommandButton9.Visible = False 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = False 'copy Thu 2
        Sheet2.CommandButton12.Visible = False 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = False 'copy Fri 2
        Sheet2.CommandButton15.Visible = False 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = False 'copy Sat 2
        Sheet2.CommandButton18.Visible = False 'copy Sat 3
       
        Sheet2.Columns("G:Z").EntireColumn.Hidden = True
       
    '2 week rotation
        Else
        If Sheet1.ComboBox5 = "2" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:J").EntireColumn.Hidden = False
        Sheet2.Columns("K:Z").EntireColumn.Hidden = True
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = True 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = False 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = False 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
                     
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = False 'copy Mon 2
        Sheet2.CommandButton3.Visible = False 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = False 'copy Tue 2
        Sheet2.CommandButton6.Visible = False 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = False 'copy Wed 2
        Sheet2.CommandButton9.Visible = False 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = False 'copy Thu 2
        Sheet2.CommandButton12.Visible = False 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = False 'copy Fri 2
        Sheet2.CommandButton15.Visible = False 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = False 'copy Sat 2
        Sheet2.CommandButton18.Visible = False 'copy Sat 3
       
    '3 week rotation
        Else
        If Sheet1.ComboBox5 = "3" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:N").EntireColumn.Hidden = False
        Sheet2.Columns("O:Z").EntireColumn.Hidden = True
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = True 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = True 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = True 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = False 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = False 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = False 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
                     
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = False 'copy Mon 2
        Sheet2.CommandButton3.Visible = False 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = False 'copy Tue 2
        Sheet2.CommandButton6.Visible = False 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = False 'copy Wed 2
        Sheet2.CommandButton9.Visible = False 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = False 'copy Thu 2
        Sheet2.CommandButton12.Visible = False 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = False 'copy Fri 2
        Sheet2.CommandButton15.Visible = False 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = False 'copy Sat 2
        Sheet2.CommandButton18.Visible = False 'copy Sat 3


       
    '4 week rotation
        Else
        If Sheet1.ComboBox5 = "4" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:R").EntireColumn.Hidden = False
        Sheet2.Columns("S:Z").EntireColumn.Hidden = True
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = True 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = True 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = True 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = True 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = True 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = True 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = False 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = False 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = False 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = False 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
                     
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = False 'copy Mon 2
        Sheet2.CommandButton3.Visible = False 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = False 'copy Tue 2
        Sheet2.CommandButton6.Visible = False 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = False 'copy Wed 2
        Sheet2.CommandButton9.Visible = False 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = False 'copy Thu 2
        Sheet2.CommandButton12.Visible = False 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = False 'copy Fri 2
        Sheet2.CommandButton15.Visible = False 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = False 'copy Sat 2
        Sheet2.CommandButton18.Visible = False 'copy Sat 3
          
    '5 wk rotation
        Else
        If Sheet1.ComboBox5 = "5" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:V").EntireColumn.Hidden = False
        Sheet2.Columns("W:Z").EntireColumn.Hidden = True
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = True 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = True 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = True 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = True 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = True 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = True 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = True 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = True 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = True 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = True 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = False 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = False 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = False 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = False 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = False 'Wk6 copy week 5 NS
                     
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = False 'copy Mon 2
        Sheet2.CommandButton3.Visible = False 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = False 'copy Tue 2
        Sheet2.CommandButton6.Visible = False 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = False 'copy Wed 2
        Sheet2.CommandButton9.Visible = False 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = False 'copy Thu 2
        Sheet2.CommandButton12.Visible = False 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = False 'copy Fri 2
        Sheet2.CommandButton15.Visible = False 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = False 'copy Sat 2
        Sheet2.CommandButton18.Visible = False 'copy Sat 3




    '6 wk rotation
        Else
        If Sheet1.ComboBox5 = "6" Then
        Sheet1.Range("B5").Copy
        Sheet2.Activate
        Sheet2.Range("B4").PasteSpecial xlPasteValues
        Sheet2.Columns("G:Z").EntireColumn.Hidden = False
       
        'split copy weeks buttons
        Sheet2.CommandButton19.Visible = False 'Wk2 copy week 1
        Sheet2.CommandButton20.Visible = False 'Wk3 copy week 1
        Sheet2.CommandButton21.Visible = False 'Wk3 copy week 2
        Sheet2.CommandButton22.Visible = False 'Wk4 copy week 1
        Sheet2.CommandButton23.Visible = False 'Wk4 copy week 2
        Sheet2.CommandButton24.Visible = False 'Wk4 copy week 3
        Sheet2.CommandButton25.Visible = False 'Wk5 copy week 1
        Sheet2.CommandButton26.Visible = False 'Wk5 copy week 2
        Sheet2.CommandButton27.Visible = False 'Wk5 copy week 3
        Sheet2.CommandButton28.Visible = False 'Wk5 copy week 4
        Sheet2.CommandButton29.Visible = False 'Wk6 copy week 1
        Sheet2.CommandButton30.Visible = False 'Wk6 copy week 2
        Sheet2.CommandButton31.Visible = False 'Wk6 copy week 3
        Sheet2.CommandButton32.Visible = False 'Wk6 copy week 4
        Sheet2.CommandButton33.Visible = False 'Wk6 copy week 5
       
        'no split copy weeks buttons
        Sheet2.CommandButton35.Visible = True 'Wk2 copy week 1 NS
        Sheet2.CommandButton36.Visible = True 'Wk3 copy week 1 NS
        Sheet2.CommandButton37.Visible = True 'Wk3 copy week 2 NS
        Sheet2.CommandButton38.Visible = True 'Wk4 copy week 1 NS
        Sheet2.CommandButton39.Visible = True 'Wk4 copy week 2 NS
        Sheet2.CommandButton40.Visible = True 'Wk4 copy week 3 NS
        Sheet2.CommandButton41.Visible = True 'Wk5 copy week 1 NS
        Sheet2.CommandButton42.Visible = True 'Wk5 copy week 2 NS
        Sheet2.CommandButton43.Visible = True 'Wk5 copy week 3 NS
        Sheet2.CommandButton44.Visible = True 'Wk5 copy week 4 NS
        Sheet2.CommandButton45.Visible = True 'Wk6 copy week 1 NS
        Sheet2.CommandButton46.Visible = True 'Wk6 copy week 2 NS
        Sheet2.CommandButton47.Visible = True 'Wk6 copy week 3 NS
        Sheet2.CommandButton48.Visible = True 'Wk6 copy week 4 NS
        Sheet2.CommandButton49.Visible = True 'Wk6 copy week 5 NS
                     
        'copy day segment buttons
        Sheet2.CommandButton1.Visible = True 'copy Mon 1
        Sheet2.CommandButton2.Visible = False 'copy Mon 2
        Sheet2.CommandButton3.Visible = False 'copy Mon 3
        Sheet2.CommandButton4.Visible = True 'copy Tue 1
        Sheet2.CommandButton5.Visible = False 'copy Tue 2
        Sheet2.CommandButton6.Visible = False 'copy Tue 3
        Sheet2.CommandButton7.Visible = True 'copy Wed 1
        Sheet2.CommandButton8.Visible = False 'copy Wed 2
        Sheet2.CommandButton9.Visible = False 'copy Wed 3
        Sheet2.CommandButton10.Visible = True 'copy Thu 1
        Sheet2.CommandButton11.Visible = False 'copy Thu 2
        Sheet2.CommandButton12.Visible = False 'copy Thu 3
        Sheet2.CommandButton13.Visible = True 'copy Fri 1
        Sheet2.CommandButton14.Visible = False 'copy Fri 2
        Sheet2.CommandButton15.Visible = False 'copy Fri 3
        Sheet2.CommandButton16.Visible = True 'copy Sat 1
        Sheet2.CommandButton17.Visible = False 'copy Sat 2
        Sheet2.CommandButton18.Visible = False 'copy Sat 3
        End If
        End If
        End If
        End If
        End If
        End If
       
End If
End If


Application.ScreenUpdating = True

Where have I gone wrong please?
 

Attachments

  • 3 wk split pre-F5.png
    3 wk split pre-F5.png
    43.2 KB · Views: 25
  • 3wk split post-F5.png
    3wk split post-F5.png
    36.1 KB · Views: 18
  • 6wk no split post-F5.png
    6wk no split post-F5.png
    126.1 KB · Views: 21
  • 6wk no split pre-F5.png
    6wk no split pre-F5.png
    120.7 KB · Views: 29
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Hi and welcome to MrExcel.

What is the problem?
The buttons move out of place? Then check the button properties. Select "Move and size with cell" or "Don't move or size with cells" and try again.

1637683534340.png


Note: you could simplify the code if at the beginning you hide all the buttons and only make visible the ones you need according to the option.
 
Upvote 0
T
DanteAmor said:
Hi and welcome to MrExcel.

What is the problem?
The buttons move out of place? Then check the button properties. Select "Move and size with cell" or "Don't move or size with cells" and try again.

View attachment 51884

Note: you could simplify the code if at the beginning you hide all the buttons and only make visible the ones you need according to the option.
Click to expand...
The problem is when the number of weeks or shift split are selected the command buttons don’t hide/unhindered as per the code unless I go into VBA and F5 the code above, however the other changes occur. I have no other code that could be over riding this one. I have checked several times. I just can’t seem to sort this bit out
 
Upvote 0
The code is simplified in this way. Try and comment.

VBA Code: 
Sub ShiftView()
  Dim i As Long
  Application.ScreenUpdating = False
  
  Sheet2.Select
  Range("B4") = Sheet1.Range("B5")
  For i = 1 To 49   'hide all
    If i <> 34 Then Sheet2.OLEObjects(i).Visible = False
  Next
  Columns("G:Z").EntireColumn.Hidden = False
  '
  If Sheet1.Range("D33") = True Then
    '**********    split shifts    **********
    Range("B6, B9, B12, B15, B18, B21, B24") = Array("Monday Split 1", "Tuesday Split 1", _
      "Wednesday Split 1", "Thursday Split 1", "Friday Split 1", "Saturday Split 1", "Sunday Split 1")
    Range("A7:A8, A10:A11, A13:A14, 16:17, 19:20, 22:23, 25:26").EntireRow.Hidden = False
    
    'buttons 1 to 18
    For i = 1 To 18
      Sheet2.OLEObjects(i).Visible = True
    Next

    'buttons 19 to 33
    Select Case Sheet1.ComboBox5.Value
      Case "1": Call visiblebuttons("G:Z", True, 1, 0, True)
      Case "2": Call visiblebuttons("K:Z", True, 19, 19, True)
      Case "3": Call visiblebuttons("O:Z", True, 19, 21, True)
      Case "4": Call visiblebuttons("S:Z", True, 19, 24, True)
      Case "5": Call visiblebuttons("W:Z", True, 19, 28, True)
      Case "6": Call visiblebuttons("G:Z", False, 19, 28, True) 'Should it be until 35?
    End Select
    
    'buttons 35 to 49 all hide
    
  Else
  
    '**********    None split shifts        **********
    If Sheet1.Range("E33") = True Then
      Range("B6, B9, B12, B15, B18, B21, B24") = Array("Monday", "Tuesday", _
        "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
      Range("A7:A8, A10:A11, A13:A14, 16:17, 19:20, 22:23, 25:26").EntireRow.Hidden = True
      
      'buttons 1 to 18
      For i = 1 To 18
        Select Case i
          Case 1, 4, 7, 10, 13, 16
            Sheet2.OLEObjects(i).Visible = True
        End Select
      Next
      
      'buttons 19 to 33 all hide
      
      'buttons 35 to 49
      Select Case Sheet1.ComboBox5.Value
        Case "1": Call visiblebuttons("G:Z", True, 1, 0, True)
        Case "2": Call visiblebuttons("K:Z", True, 35, 35, True)
        Case "3": Call visiblebuttons("O:Z", True, 35, 37, True)
        Case "4": Call visiblebuttons("S:Z", True, 35, 40, True)
        Case "5": Call visiblebuttons("W:Z", True, 35, 44, True)
        Case "6": Call visiblebuttons("G:Z", False, 35, 49, True)
      End Select
      
    End If
  End If
  Application.ScreenUpdating = True
End Sub

Sub visiblebuttons(cols As String, cvis As Boolean, x As Long, y As Long, vis As Boolean)
  Dim i As Long
  Columns(cols).EntireColumn.Hidden = cvis
  For i = x To y
    Sheet2.OLEObjects(i).Visible = vis
  Next
End Sub
 
Upvote 0
Solution
DanteAmor said:
The code is simplified in this way. Try and comment.

VBA Code: 
Sub ShiftView()
  Dim i As Long
  Application.ScreenUpdating = False
 
  Sheet2.Select
  Range("B4") = Sheet1.Range("B5")
  For i = 1 To 49   'hide all
    If i <> 34 Then Sheet2.OLEObjects(i).Visible = False
  Next
  Columns("G:Z").EntireColumn.Hidden = False
  '
  If Sheet1.Range("D33") = True Then
    '**********    split shifts    **********
    Range("B6, B9, B12, B15, B18, B21, B24") = Array("Monday Split 1", "Tuesday Split 1", _
      "Wednesday Split 1", "Thursday Split 1", "Friday Split 1", "Saturday Split 1", "Sunday Split 1")
    Range("A7:A8, A10:A11, A13:A14, 16:17, 19:20, 22:23, 25:26").EntireRow.Hidden = False
   
    'buttons 1 to 18
    For i = 1 To 18
      Sheet2.OLEObjects(i).Visible = True
    Next

    'buttons 19 to 33
    Select Case Sheet1.ComboBox5.Value
      Case "1": Call visiblebuttons("G:Z", True, 1, 0, True)
      Case "2": Call visiblebuttons("K:Z", True, 19, 19, True)
      Case "3": Call visiblebuttons("O:Z", True, 19, 21, True)
      Case "4": Call visiblebuttons("S:Z", True, 19, 24, True)
      Case "5": Call visiblebuttons("W:Z", True, 19, 28, True)
      Case "6": Call visiblebuttons("G:Z", False, 19, 28, True) 'Should it be until 35?
    End Select
   
    'buttons 35 to 49 all hide
   
  Else
 
    '**********    None split shifts        **********
    If Sheet1.Range("E33") = True Then
      Range("B6, B9, B12, B15, B18, B21, B24") = Array("Monday", "Tuesday", _
        "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
      Range("A7:A8, A10:A11, A13:A14, 16:17, 19:20, 22:23, 25:26").EntireRow.Hidden = True
     
      'buttons 1 to 18
      For i = 1 To 18
        Select Case i
          Case 1, 4, 7, 10, 13, 16
            Sheet2.OLEObjects(i).Visible = True
        End Select
      Next
     
      'buttons 19 to 33 all hide
     
      'buttons 35 to 49
      Select Case Sheet1.ComboBox5.Value
        Case "1": Call visiblebuttons("G:Z", True, 1, 0, True)
        Case "2": Call visiblebuttons("K:Z", True, 35, 35, True)
        Case "3": Call visiblebuttons("O:Z", True, 35, 37, True)
        Case "4": Call visiblebuttons("S:Z", True, 35, 40, True)
        Case "5": Call visiblebuttons("W:Z", True, 35, 44, True)
        Case "6": Call visiblebuttons("G:Z", False, 35, 49, True)
      End Select
     
    End If
  End If
  Application.ScreenUpdating = True
End Sub

Sub visiblebuttons(cols As String, cvis As Boolean, x As Long, y As Long, vis As Boolean)
  Dim i As Long
  Columns(cols).EntireColumn.Hidden = cvis
  For i = x To y
    Sheet2.OLEObjects(i).Visible = vis
  Next
End Sub
Click to expand...
Thank you for this simplification. I didn’t know this was possible.

I am still having the issue where if the options are changed the buttons of the previous choice still show (along with the current option) until I go into the code and refresh it with F5.
 
Upvote 0
Found the issue was in another part of the code.

Thank you so much for the awesome and tidy coding you gave me as well! Love it!
 
Upvote 0
I'm glad to help you. I appreciate your kind comments.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
386
bobsan42
bobsan42
G
  • Question Question
Creating a Word Doc using Excel VBA
Replies
2
Views
328
rikvny02
R
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
125
Northern NY Design
N
S
  • Solved
Copy values only without specific column formats VBA
Replies
9
Views
406
sofas
S
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
468
dmt32
D

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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