Return and stop code after running on last sheet in the work book

lonybrown

Board Regular
Joined
Mar 8, 2021
Messages
68
Office Version
  1. 365
Platform
  1. Windows
How can I finish this code to return to the first sheet in my work book once it updates the last sheet in the workbook?

1EmployeeForemanBi-Weekly Timesheet
1
Date WorkedStart TimeEnd TimeJob NumberDescription of WorkRegular Hrs.Travel Hrs.1.5 OT2 OT
Dec 27, 2020​
7:00 AM7:00 AM00
Dec 28, 2020​
7:00 AM7:00 AM00
Dec 29, 2020​
7:00 AM7:00 AM00
Dec 30, 2020​
7:00 AM7:00 AM00
Dec 31, 2020​
7:00 AM7:00 AM00
Jan 1, 2021​
12:00 AM0
Jan 2, 2021​
12:00 AM0
Jan 3, 2021​
7:00 AM7:00 AM00
Jan 4, 2021​
7:00 AM7:00 AM00
Jan 5, 2021​
7:00 AM7:00 AM00
Jan 6, 2021​
7:00 AM7:00 AM00
Jan 7, 2021​
7:00 AM7:00 AM00
Jan 8, 2021​
12:00 AM0
Jan 9, 2021​
12:00 AM0
TOTAL HOURS0000
PLEASE BANK0
PAY OUT BANKED HOURS0
TOTAL PAID HOURS0000
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Here is the code

What code?
VBA Code:
Sub Macro1()


    Dim ws As Worksheet
    Application.ScreenUpdating = False
   


    For Each ws In ActiveWorkbook.Worksheets

        ActiveSheet.Activate
        ActiveSheet.Unprotect "TEST"


       
       
        With ws
            .Cells.EntireColumn.Hidden = False
ActiveSheet.Activate
ActiveSheet.Unprotect "TEST"


        Select Case .Range("A2").Value


            Case 1
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("G:G,H:I,N:MZ").EntireColumn.Hidden = True
            Case 2
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:N,T:V,AA:MZ").EntireColumn.Hidden = True
            Case 3
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:AA,AG:AI,AN:MZ").EntireColumn.Hidden = True
            Case 4
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:AN,AT:AV,BA:MZ").EntireColumn.Hidden = True
            Case 5
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:BA,BG:BI,BN:MZ").EntireColumn.Hidden = True
            Case 6
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:BN,BT:BV,CA:MZ").EntireColumn.Hidden = True
            Case 7
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:CA,CG:CI,CM:MZ").EntireColumn.Hidden = True
            Case 8
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:CN,CT:CV,DA:MZ").EntireColumn.Hidden = True
            Case 9
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:DA,DG:DI,DN:MZ").EntireColumn.Hidden = True
            Case 10
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:DN,DT:DV,EA:MZ").EntireColumn.Hidden = True
            Case 11
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:EA,EG:EI,EN:MZ").EntireColumn.Hidden = True
            Case 12
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:EN,ET:EV,FA:MZ").EntireColumn.Hidden = True
            Case 13
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:FA,FG:FI,FN:MZ").EntireColumn.Hidden = True
            Case 14
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:FN,FT:FV,GA:MZ").EntireColumn.Hidden = True
            Case 15
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:GA,GG:GI,GN:MZ").EntireColumn.Hidden = True
            Case 16
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:GN,GT:GV,HA:MZ").EntireColumn.Hidden = True
            Case 17
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:HA,HG:HI,HN:MZ").EntireColumn.Hidden = True
            Case 18
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:HN,HT:HV,IA:MZ").EntireColumn.Hidden = True
            Case 19
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:IA,IG:II,IN:MZ").EntireColumn.Hidden = True
            Case 20
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:IN,IT:IV,JA:MZ").EntireColumn.Hidden = True
            Case 21
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:JA,JG:JI,JN:MZ").EntireColumn.Hidden = True
            Case 22
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:JN,JT:JV,KA:MZ").EntireColumn.Hidden = True
            Case 23
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:KA,KG:KI,KN:MZ").EntireColumn.Hidden = True
            Case 24
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:KN,KT:KV,LA:MZ").EntireColumn.Hidden = True
            Case 25
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:LA,LG:LI,LN:MZ").EntireColumn.Hidden = True
            Case 26
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:LN,LT:LV,MA:MZ").EntireColumn.Hidden = True
            Case 27
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:MA,MG:MI,MN:MZ").EntireColumn.Hidden = True
            Case 28
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:MN,MT:MV,NA:MZ").EntireColumn.Hidden = True

        End Select
ActiveSheet.Activate
ActiveSheet.Protect "TEST"

           
Range("A2").Select
Selection.Copy



ActiveSheet.Next.Select


Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False





  
End With
      
Next



Application.ScreenUpdating = True
   
   
End Sub
 
Upvote 0
When posting code please use code tags How to Post Your VBA Code
How about
VBA Code:
Sub Macro1()


    Dim ws As Worksheet
    Application.ScreenUpdating = False
   


    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect "TEST"




        With ws
            .Cells.EntireColumn.Hidden = False


        Select Case .Range("A2").Value


            Case 1
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("G:G,H:I,N:MZ").EntireColumn.Hidden = True
            Case 2
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:N,T:V,AA:MZ").EntireColumn.Hidden = True
            Case 3
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:AA,AG:AI,AN:MZ").EntireColumn.Hidden = True
            Case 4
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:AN,AT:AV,BA:MZ").EntireColumn.Hidden = True
            Case 5
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:BA,BG:BI,BN:MZ").EntireColumn.Hidden = True
            Case 6
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:BN,BT:BV,CA:MZ").EntireColumn.Hidden = True
            Case 7
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:CA,CG:CI,CM:MZ").EntireColumn.Hidden = True
            Case 8
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:CN,CT:CV,DA:MZ").EntireColumn.Hidden = True
            Case 9
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:DA,DG:DI,DN:MZ").EntireColumn.Hidden = True
            Case 10
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:DN,DT:DV,EA:MZ").EntireColumn.Hidden = True
            Case 11
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:EA,EG:EI,EN:MZ").EntireColumn.Hidden = True
            Case 12
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:EN,ET:EV,FA:MZ").EntireColumn.Hidden = True
            Case 13
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:FA,FG:FI,FN:MZ").EntireColumn.Hidden = True
            Case 14
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:FN,FT:FV,GA:MZ").EntireColumn.Hidden = True
            Case 15
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:GA,GG:GI,GN:MZ").EntireColumn.Hidden = True
            Case 16
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:GN,GT:GV,HA:MZ").EntireColumn.Hidden = True
            Case 17
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:HA,HG:HI,HN:MZ").EntireColumn.Hidden = True
            Case 18
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:HN,HT:HV,IA:MZ").EntireColumn.Hidden = True
            Case 19
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:IA,IG:II,IN:MZ").EntireColumn.Hidden = True
            Case 20
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:IN,IT:IV,JA:MZ").EntireColumn.Hidden = True
            Case 21
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:JA,JG:JI,JN:MZ").EntireColumn.Hidden = True
            Case 22
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:JN,JT:JV,KA:MZ").EntireColumn.Hidden = True
            Case 23
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:KA,KG:KI,KN:MZ").EntireColumn.Hidden = True
            Case 24
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:KN,KT:KV,LA:MZ").EntireColumn.Hidden = True
            Case 25
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:LA,LG:LI,LN:MZ").EntireColumn.Hidden = True
            Case 26
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:LN,LT:LV,MA:MZ").EntireColumn.Hidden = True
            Case 27
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:MA,MG:MI,MN:MZ").EntireColumn.Hidden = True
            Case 28
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:MN,MT:MV,NA:MZ").EntireColumn.Hidden = True

        End Select
ws.Protect "TEST"

If ws.Index < Worksheets.Count Then
   ws.Range("A2").Copy Sheets(ws.Index + 1).Range("A2")
End If




  
End With
      
Next



Application.ScreenUpdating = True
   
   
End Sub
 
Upvote 0
Solution
When posting code please use code tags How to Post Your VBA Code
How about
VBA Code:
Sub Macro1()


    Dim ws As Worksheet
    Application.ScreenUpdating = False
  


    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect "TEST"




        With ws
            .Cells.EntireColumn.Hidden = False


        Select Case .Range("A2").Value


            Case 1
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("G:G,H:I,N:MZ").EntireColumn.Hidden = True
            Case 2
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:N,T:V,AA:MZ").EntireColumn.Hidden = True
            Case 3
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:AA,AG:AI,AN:MZ").EntireColumn.Hidden = True
            Case 4
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:AN,AT:AV,BA:MZ").EntireColumn.Hidden = True
            Case 5
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:BA,BG:BI,BN:MZ").EntireColumn.Hidden = True
            Case 6
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:BN,BT:BV,CA:MZ").EntireColumn.Hidden = True
            Case 7
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:CA,CG:CI,CM:MZ").EntireColumn.Hidden = True
            Case 8
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:CN,CT:CV,DA:MZ").EntireColumn.Hidden = True
            Case 9
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:DA,DG:DI,DN:MZ").EntireColumn.Hidden = True
            Case 10
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:DN,DT:DV,EA:MZ").EntireColumn.Hidden = True
            Case 11
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:EA,EG:EI,EN:MZ").EntireColumn.Hidden = True
            Case 12
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:EN,ET:EV,FA:MZ").EntireColumn.Hidden = True
            Case 13
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:FA,FG:FI,FN:MZ").EntireColumn.Hidden = True
            Case 14
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:FN,FT:FV,GA:MZ").EntireColumn.Hidden = True
            Case 15
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:GA,GG:GI,GN:MZ").EntireColumn.Hidden = True
            Case 16
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:GN,GT:GV,HA:MZ").EntireColumn.Hidden = True
            Case 17
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:HA,HG:HI,HN:MZ").EntireColumn.Hidden = True
            Case 18
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:HN,HT:HV,IA:MZ").EntireColumn.Hidden = True
            Case 19
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:IA,IG:II,IN:MZ").EntireColumn.Hidden = True
            Case 20
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:IN,IT:IV,JA:MZ").EntireColumn.Hidden = True
            Case 21
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:JA,JG:JI,JN:MZ").EntireColumn.Hidden = True
            Case 22
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:JN,JT:JV,KA:MZ").EntireColumn.Hidden = True
            Case 23
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:KA,KG:KI,KN:MZ").EntireColumn.Hidden = True
            Case 24
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:KN,KT:KV,LA:MZ").EntireColumn.Hidden = True
            Case 25
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:LA,LG:LI,LN:MZ").EntireColumn.Hidden = True
            Case 26
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:LN,LT:LV,MA:MZ").EntireColumn.Hidden = True
            Case 27
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:MA,MG:MI,MN:MZ").EntireColumn.Hidden = True
            Case 28
                .Range("A:A,H:MZ").EntireColumn.Hidden = False
                .Range("B:MN,MT:MV,NA:MZ").EntireColumn.Hidden = True

        End Select
ws.Protect "TEST"

If ws.Index < Worksheets.Count Then
   ws.Range("A2").Copy Sheets(ws.Index + 1).Range("A2")
End If




 
End With
     
Next



Application.ScreenUpdating = True
  
  
End Sub
that works perfect Thanks so much.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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