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

lonybrown

New Member
Joined
Mar 8, 2021
Messages
29
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

lonybrown

New Member
Joined
Mar 8, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

lonybrown

New Member
Joined
Mar 8, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,716
Members
417,107
Latest member
derekMG

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