Code to insert new sheet

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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.

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]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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:
Code:
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
 

Forum statistics

Threads
1,141,757
Messages
5,708,355
Members
421,566
Latest member
7Nabisco

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