Adding to an existing MACRO

N

Northern NY Design

New Member
Joined
Sep 24, 2024
Messages
25
Office Version
  1. 365
Platform
  1. Windows
I would like to add the following to an existing MACRO. (I LEFT 10 LINE SPACES where I am trying to add the code.)

In the CURRENT WEEK tab I need to have it HIGHLIGHT GN6:IA104 and COPY it. Then I need to PASTE that data to CL6:EI104 (same tab) and then CLEAR the data that was in GN6:IA104


VBA Code: 
Sub RESETTRACKER()
    If MsgBox("This is your friendly Lon-bot, reminding you that if you click YES, this Tracker will create an archive of the CURRENT WEEK and save it as LAST WEEK, and will then move NEXT MONDAY data to MONDAY and wipe the rest of the tracker to start a new week. Are you want to proceed? THIS CANNOT BE UNDONE", vbYesNo) = vbYes Then
'
' RESETTRACKER Macro
' This MACRO will reset the ENTIRE Tracker for a new week, making a copy of the CURRENT WEEK for archive.
Sheets("CURRENT WEEK").Select
ActiveSheet.Unprotect
Sheets("SUN").Select
 ActiveSheet.Unprotect
Sheets("MON").Select
ActiveSheet.Unprotect
Sheets("TUE").Select
ActiveSheet.Unprotect
Sheets("WED").Select
ActiveSheet.Unprotect
Sheets("THU").Select
ActiveSheet.Unprotect
Sheets("FRI").Select
ActiveSheet.Unprotect
Sheets("SAT").Select
ActiveSheet.Unprotect
Sheets("NEXT MON").Select
ActiveSheet.Unprotect
Sheets("CURRENT WEEK").Select
Sheets("CURRENT WEEK").Copy Before:=Sheets(19)
Sheets("CURRENT WEEK (2)").Select
Sheets("CURRENT WEEK (2)").Name = "LAST WEEK"
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Selection.Copy
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:J3").Select
Sheets("LAST WEEK").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("CURRENT WEEK").Select
Rows("35:106").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=2
Rows("34:107").Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=-2
Range("EJ6:ES7").Select
Range("EJ6:ES105").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-85
Range("CB6").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False








Range("CL6").Select
Range("ES105").Select
Range("CL6:ES105").Select
Range("ES105").Activate
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=0
Range("S6:T6").Select
Sheets("CURRENT WEEK").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("SUN").Select
ActiveWindow.SmallScroll Down:=8
Rows("34:105").Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=-8
Range("O5:Q5").Select
Range("O5:Q104").Select
Selection.ClearContents
Range("S5:U5").Select
Range("S5:U104").Select
Selection.ClearContents
Selection.ClearContents
Range("W5:Y5").Select
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-68
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=78
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-78
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=73
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
Range("AI5:AT5").Select
Range("AI5:AT104").Select
Selection.ClearContents
Range("I5:J5").Select
Sheets("SUN").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("NEXT MON").Select
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=8
Rows("34:105").Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=-8
Range("O5:Q5").Select
Range("O5:Q104").Select
Selection.Copy
Sheets("MON").Select
Range("O5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll Down:=-68
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=81
Range("S5:U104").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-81
Sheets("MON").Select
Range("S5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("NEXT MON").Select
Range("W5:Y5").Select
Range("W5:Y5,Y104").Select
Range("Y104").Activate
Range("W5:Y5,W5:Y104").Select
Range("Y104").Activate
Application.CutCopyMode = False
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=70
Range("W5:Y104").Select
Selection.Copy
Sheets("MON").Select
Range("W5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll Down:=-70
ActiveWindow.SmallScroll ToRight:=11
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=76
Range("AA5:AC104").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-76
Sheets("MON").Select
Range("AA5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll ToRight:=7
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=8
Range("AE5:AG104").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MON").Select
Range("AE5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll Down:=-68
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=78
Range("AI5:AT104").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MON").Select
ActiveWindow.SmallScroll Down:=0
Range("AI5").Select
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll Down:=-78
Range("AI5:AM5").Select
Range("AI5:AM104").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MON").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=12
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.SmallScroll Down:=-68
Range("AN5:AT5").Select
ActiveWindow.SmallScroll Down:=81
Range("AN5:AT104").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MON").Select
Range("AN5:AT5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("NEXT MON").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-81
Range("C5:D5").Select
ActiveWindow.SmallScroll Down:=81
Range("C5:D104").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MON").Select
ActiveWindow.SmallScroll Down:=0
Range("C5:D5").Select
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=2
Sheets("MON").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True
Sheets("NEXT MON").Select
ActiveWindow.SmallScroll Down:=-81
Selection.ClearContents
Range("O5:Q104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-67
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=81
Selection.ClearContents
Range("U104").Select
Selection.ClearContents
Selection.ClearContents
Selection.ClearContents
Selection.ClearContents
Selection.ClearContents
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-81
Range("S5:U104").Select
Range("U104").Activate
Selection.ClearContents
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=84
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-84
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=73
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=70
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-70
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=84
Range("AI5:AT104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-84
Range("I5:J5").Select
Sheets("NEXT MON").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("TUE").Select
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=0
Rows("34:105").Select
Selection.EntireRow.Hidden = False
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=81
Range("O5:Q104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-81
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=70
Range("S5:U104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-70
ActiveWindow.SmallScroll ToRight:=9
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=78
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-78
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=81
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-81
ActiveWindow.SmallScroll ToRight:=4
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=81
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-81
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=73
Range("AI5:AT104").Select
Selection.ClearContents
Range("C5:D104").Select
Selection.ClearContents
Range("A5:B5").Select
Sheets("TUE").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True
Sheets("WED").Select
Rows("34:105").Select
Selection.EntireRow.Hidden = False
Range("C5:D5").Select
ActiveWindow.SmallScroll Down:=76
Range("C5:D104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-76
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=73
Range("O5:Q104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=73
Range("S5:U104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=67
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-67
ActiveWindow.SmallScroll ToRight:=6
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=76
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-76
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=76
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-76
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=73
Range("AI5:AT104").Select
Selection.ClearContents
Range("I5:J5").Select
Sheets("WED").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True
Sheets("THU").Select
Rows("34:105").Select
Selection.EntireRow.Hidden = False
Range("C5:D5").Select
ActiveWindow.SmallScroll Down:=78
Range("C5:D104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-78
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=84
Range("O5:Q104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-84
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=84
Range("S5:U104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-84
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=78
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-78
ActiveWindow.SmallScroll ToRight:=8
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=84
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-84
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=70
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-70
ActiveWindow.SmallScroll ToRight:=9
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=76
Range("AI5:AT104").Select
Selection.ClearContents
Range("I5:J5").Select
Sheets("THU").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True
Sheets("FRI").Select
Rows("34:105").Select
Selection.EntireRow.Hidden = False
Range("C5:D5").Select
ActiveWindow.SmallScroll Down:=76
Range("C5:D104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-76
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=81
Range("O5:Q104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-81
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=78
Range("S5:U104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-78
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=67
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-67
ActiveWindow.SmallScroll ToRight:=14
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=76
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-76
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=76
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-76
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=70
Range("AI5:AT104").Select
Selection.ClearContents
Range("I5:J5").Select
Sheets("FRI").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True
Sheets("SAT").Select
Rows("34:105").Select
Selection.EntireRow.Hidden = False
Range("C5:D5").Select
ActiveWindow.SmallScroll Down:=73
Range("C5:D104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
Range("O5:Q5").Select
ActiveWindow.SmallScroll Down:=70
Range("O5:Q104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-70
Range("S5:U5").Select
ActiveWindow.SmallScroll Down:=70
Range("S5:U104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-70
Range("W5:Y5").Select
ActiveWindow.SmallScroll Down:=73
Range("W5:Y104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
Range("AA5:AC5").Select
ActiveWindow.SmallScroll Down:=70
Range("AA5:AC104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-70
Range("AE5:AG5").Select
ActiveWindow.SmallScroll Down:=78
Range("AE5:AG104").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-78
Range("AI5:AT5").Select
ActiveWindow.SmallScroll Down:=76
Range("AI5:AT104").Select
Selection.ClearContents
Range("I5:J5").Select
Sheets("SAT").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If

End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try this in a copy of your workbook.
There seem to be some inconsistencies in Row number, I can see 104 (most common) but then also 105,106 & 107. I think it will be more obvious in this format.
The lines in blue are for the copy paste you wanted added.
I was unclear on where you wanted Last Week added.

Rich (BB code): 
Sub RESETTRACKER_Mod()

    Dim strDataRng As String, splitAddress As Variant
    Dim sht As Worksheet
    Dim i As Long
   
    If MsgBox("This is your friendly Lon-bot, reminding you that if you click YES, this Tracker will create an archive of the CURRENT WEEK and save it as LAST WEEK, and will then move NEXT MONDAY data to MONDAY and wipe the rest of the tracker to start a new week. Are you want to proceed? THIS CANNOT BE UNDONE", vbYesNo) = vbYes Then
       
        ' List of range addresses that will be copied from Next Mon to Mon and Cleared Out of new week days
        strDataRng = "C5:D104,O5:Q104,S5:U104,W5:Y104,AA5:AC104,AE5:AG104,AI5:AT104"
        splitAddress = Split(strDataRng, ",")
        '
        ' RESETTRACKER Macro
        ' This MACRO will reset the ENTIRE Tracker for a new week, making a copy of the CURRENT WEEK for archive.
        Sheets("CURRENT WEEK").Unprotect

        Sheets("MON").Unprotect

        'Sheets("CURRENT WEEK").Copy Before:=Sheets(19)
        Sheets("CURRENT WEEK").Copy Before:=Sheets(Sheets.Count)
        ActiveSheet.Name = "LAST WEEK"
       
        With Sheets("LAST WEEK")
            With .Cells
                .Locked = True
                .FormulaHidden = False
                .Copy
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            End With
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With
            
        With Sheets("CURRENT WEEK")
            '.Rows("35:106").EntireRow.Hidden = True          ' Superceded by next line
            .Rows("34:107").EntireRow.Hidden = False

            .Range("EJ6:ES105").Copy

            .Range("CB6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            .Range("CL6:ES105").ClearContents
           
            ' XXX Added Copy Paste XXX
            .Range("GN6:IA104").Copy
            .Range("CL6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            .Range("GN6:IA104").ClearContents

            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With
       
        With Sheets("NEXT MON")
            .Rows("34:105").EntireRow.Hidden = False
        End With
       
        ' Copy the values from Next Mon to the equivalent cells in Mon
        For i = 0 To UBound(splitAddress)
            Sheets("MON").Range(splitAddress(i)).Value = Sheets("NEXT MON").Range(splitAddress(i)).Value
        Next i

        Sheets("MON").Protect DrawingObjects:=True, Contents:=True, _
                        Scenarios:=True, AllowSorting:=True
       
        For Each sht In ActiveWorkbook.Worksheets
            Select Case sht.Name
                Case "TUE", "WED", "THU", "FRI", "SAT", "SUN", "NEXT MON"
                    With sht
                        .Unprotect
                       
                        .Rows("34:105").EntireRow.Hidden = False
                       
                        .Range(strDataRng).ClearContents
                     
                        .Protect DrawingObjects:=False, Contents:=True, _
                            Scenarios:=False, AllowSorting:=True
                    End With
               
                Case Else
            End Select
       
        Next sht

    End If

End Sub
 
Upvote 0
Alex Blakenburg said:
Try this in a copy of your workbook.
There seem to be some inconsistencies in Row number, I can see 104 (most common) but then also 105,106 & 107. I think it will be more obvious in this format.
The lines in blue are for the copy paste you wanted added.
I was unclear on where you wanted Last Week added.

Rich (BB code): 
Sub RESETTRACKER_Mod()

    Dim strDataRng As String, splitAddress As Variant
    Dim sht As Worksheet
    Dim i As Long
  
    If MsgBox("This is your friendly Lon-bot, reminding you that if you click YES, this Tracker will create an archive of the CURRENT WEEK and save it as LAST WEEK, and will then move NEXT MONDAY data to MONDAY and wipe the rest of the tracker to start a new week. Are you want to proceed? THIS CANNOT BE UNDONE", vbYesNo) = vbYes Then
      
        ' List of range addresses that will be copied from Next Mon to Mon and Cleared Out of new week days
        strDataRng = "C5:D104,O5:Q104,S5:U104,W5:Y104,AA5:AC104,AE5:AG104,AI5:AT104"
        splitAddress = Split(strDataRng, ",")
        '
        ' RESETTRACKER Macro
        ' This MACRO will reset the ENTIRE Tracker for a new week, making a copy of the CURRENT WEEK for archive.
        Sheets("CURRENT WEEK").Unprotect

        Sheets("MON").Unprotect

        'Sheets("CURRENT WEEK").Copy Before:=Sheets(19)
        Sheets("CURRENT WEEK").Copy Before:=Sheets(Sheets.Count)
        ActiveSheet.Name = "LAST WEEK"
      
        With Sheets("LAST WEEK")
            With .Cells
                .Locked = True
                .FormulaHidden = False
                .Copy
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            End With
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With
           
        With Sheets("CURRENT WEEK")
            '.Rows("35:106").EntireRow.Hidden = True          ' Superceded by next line
            .Rows("34:107").EntireRow.Hidden = False

            .Range("EJ6:ES105").Copy

            .Range("CB6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            .Range("CL6:ES105").ClearContents
          
            ' XXX Added Copy Paste XXX
            .Range("GN6:IA104").Copy
            .Range("CL6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
            .Range("GN6:IA104").ClearContents

            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With
      
        With Sheets("NEXT MON")
            .Rows("34:105").EntireRow.Hidden = False
        End With
      
        ' Copy the values from Next Mon to the equivalent cells in Mon
        For i = 0 To UBound(splitAddress)
            Sheets("MON").Range(splitAddress(i)).Value = Sheets("NEXT MON").Range(splitAddress(i)).Value
        Next i

        Sheets("MON").Protect DrawingObjects:=True, Contents:=True, _
                        Scenarios:=True, AllowSorting:=True
      
        For Each sht In ActiveWorkbook.Worksheets
            Select Case sht.Name
                Case "TUE", "WED", "THU", "FRI", "SAT", "SUN", "NEXT MON"
                    With sht
                        .Unprotect
                      
                        .Rows("34:105").EntireRow.Hidden = False
                      
                        .Range(strDataRng).ClearContents
                    
                        .Protect DrawingObjects:=False, Contents:=True, _
                            Scenarios:=False, AllowSorting:=True
                    End With
              
                Case Else
            End Select
      
        Next sht

    End If

End Sub
Click to expand...
I am still working on this; it seems to be in the wrong location. I will keep troubleshooting to get it in the right spot. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro for
Replies
2
Views
96
Sanderb
S
P
  • Question Question
Help with Vba Code
Replies
3
Views
460
Coyotex3
C
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
506
Joe4
Joe4
C
  • Solved
Migrating 2 macros and sorting them
Replies
5
Views
456
Legacy 143009
L
R
  • Question Question
Macro not working after adding two new items
Replies
5
Views
431
regan_2000
R

Forum statistics

Threads
1,223,841
Messages
6,174,974
Members
452,595
Latest member
lmblane

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