I have a code below that duplicates and then renames the active sheet in excel using VBA. This works great thanks to MikeVol Now, I need to do several more things at the same time.
I have a main schedule sheet "M-D-YY" and then two supporting sheets. "M-D-YY Block" and "M-D-YY Export" both of these sheets contain info the main sheet pulls from. Could the code below be used to duplicate and rename these as well?
So this is the order in which things need to be done: (Steps one and two are solved I need help with steps 3-5)
1. Duplicate the active sheet. ("M-D-YY")
2. Rename it utilizing the actives sheets name. ("M-D-YY" plus 42 days)
3. Duplicate the Block and Export sheets that support the active sheet. ("M-D-YY Block" and "M-D-YY Export")
These sheets are hidden so the code would need to look at the active sheet name then duplicate the corresponding block and export sheets.
4. Rename both the block and export sheets using the new date "M-D-YY plus 42 days"
5. Find and replace references in the newly duplicated "M-D-YY" sheet to the newly created block sheet.
Example:
Active sheet is 5-6-24
Steps 1-2 - duplicates the active sheet "5-6-24" and then renames it "6-17-24"
Step 3-4 - Duplicates "5-6-24 Block" and 5-6-24 Export" and renames them to "6-17-24 Block" and 6-17-24 Export"
Step 5 - Find and replace any references to 5-6-24 Block and replaces with 6-17-Block in the newly created sheet from steps 1-2
Ideally this would all be done with one button click.
Below is the working code for steps 1-2. BTW this code also unprotect and deletes some cells in the active sheet. That part of the code works great.
Sub copy_and_delete_schedule()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = ActiveSheet
Dim currentDateString As String
currentDateString = ws.Name
currentDateString = Replace(currentDateString, "-", "/")
currentDateString = Replace(currentDateString, " ", "")
Dim currentDate As Date
currentDate = DateValue(currentDateString)
Dim newDate As Date
newDate = DateValue(currentDate) + 42
' ' Or
' newDate = DateAdd("d", 42, currentDate)
ws.Copy After:=ActiveSheet
ActiveSheet.Name = Format(newDate, "m-d-yy")
With ActiveSheet
.Unprotect "unicorn"
.Range("BP2,O25:BI37,O44:BI56,O63:BI71,O82:BI91,O102:BI108,O123:BI132,O139:BI144,O149:BI159,O164:BI172,O174:BI180,O182:BI184,O189:BI195,O197:BI200,O206:BI211,O213:BI215,O217:BI221,O228:BI233").ClearContents
With .Range("M25:M37,O25:BI37,M44:M56,O44:BI56,M63:M171,O63:BI71,M82:M191,O82:BI91,M102:M108,O102:BI108,M123:M132,O123:BI132,M139:M144,O139:BI144,M149:M159,O149:BI159,M164:M172,O164:BI172,M174:M180,O174:BI180")
.Locked = False
.FormulaHidden = False
End With
With .Range("M182:M184,O182:BI184,M189:M195,O189:BI195,M197:M200,O197:BI200,M206:M211,O206:BI211,M213:M215,O213:BI215,M217:M221,O217:BI221,M228:M233,O228:BI233")
.Locked = False
.FormulaHidden = False
End With
.Protect "unicorn"
Application.Goto .Cells(1, 1), True
Application.ScreenUpdating = False
End With
End Sub
Bookmarks