Code to insert new sheet

[RAM]

Hi,

Can someone please help me to update this code to insert a new sheet before running it, and other needed corrections to run the code with the new sheet.

This is to not having to insert blank sheets before running the code.

Sub NewWeek_Click()
'CompanyIncomeStatsGrid

Application.ScreenUpdating = False

'1) Verify procedure before continue msg box

   Prompt = "Are you certain that you want to continue and install a new worksheet for a new week?  The new Sheet will be based upon the old sheet and the old sheet must be complete before you continue.  The new sheet will NOT update its values if you later change earlier sheet.  You must then change all sheets that follows the earlier one you change."
    Title = "Verify Procedure"
     
    Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
    If Proceed = vbNo Then
        MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
        Exit Sub
    End If


'2) Move the sheet to a new week

ActiveSheet.Unprotect
Sheets(ActiveSheet.Index + 1).Unprotect

Range("a4", Cells.SpecialCells(xlCellTypeLastCell).Address).EntireRow. _
Copy Destination:=Sheets(ActiveSheet.Index + 1).Range("a1")
Range("a4", Cells.SpecialCells(xlCellTypeLastCell).Address).EntireColumn. _
Copy Destination:=Sheets(ActiveSheet.Index + 1).Range("a1")

CutCopyMode = False
Range("a1").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


Sheets(ActiveSheet.Index + 1).Select
Range("a1").Select

'3) Update the sheet for a new week:


 Range("C4:J4").Select
    Selection.Copy
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

 Range("C6:J6").Select
    Selection.Copy
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

 Range("C8:J8").Select
    Selection.Copy
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

 Range("C10:J10").Select
    Selection.Copy
    Range("C11").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

 Range("C12:J12").Select
    Selection.Copy
    Range("C13").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
 Range("C14:J14").Select
    Selection.Copy
    Range("C15").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    Range("C4:J4,C6:J6,C8:J8,C10:J10,C12:J12,C14:J14").ClearContents
    
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    
    MsgBox "Set Date and get busy. This is no time to rest!"

End Sub

TIA[/code]

 

[Norie]

RAM

How do you want to 'work' with the new sheet?

Will the new sheet be a copy of an existing sheet?

Where are you copying/pasting from/to?

 

[RAM]

The code simply copy existing sheet to the next sheet, and then moves down present week’s sales to the next row below. I don't need any changes in the code except I don't want to insert a new blank sheet at the end, I want the code to do that before running the rest of the code.

I always copy from the last sheet in the WB. The last sheet is the last week and when that week is over I need a new week and I run the code.

RAM

 

[Norie]

Sorry but that doesn't really clarify things, specifically where you are copying and pasting from/to.

Are you copying from the original sheet to the new sheet?

Or are you just copying from the new sheet to the new sheet?

 

[RAM]

I only work on the last sheet in the book. The last sheet is my sales worksheet. The last sheet represent present weeks sales. When the week is over I copy the last weeks sales into a new sheet and run more codes to give me free room for new sales. The code works as it does today. I just want a code that insert a new sheet at the end so I don't have to it manually.

Something like this:

Dim ws As Worksheet, wsNew As Worksheet
Set ws = ActiveSheet
ws.Unprotect

Set wsNew = Sheets.Add(after:=Sheets(Sheets.Count))

would probably do it, but I'm uncertain what other changes needs to be done and I really don't have that time to figure it out right now. I'm pretty dumb with codes.

RAM