CODEWITHSUNDEEP
vbaexcel
Ritu Mishra
Ritu Mishra

Reputation: 83

Why does my VBA code create an extra sheet when it runs?

My code works perfectly except it creates an extra sheet (whatever is next in the sequence...i.e "Sheet2".."Sheet3"...each time I run it. Could anyone help figure out why this is happening?...there should only be two Sheets Created as I have indicated in the code below:

Sub CopySheet()

Dim NewSheet As String
Dim PrevSheet As String
Dim CashWS As Worksheet
Dim MonthVal As String


NewSheet = InputBox("Which month is this Commissions statement for?")
PrevSheet = InputBox("What was the previous month?")

Worksheets(PrevSheet).Copy After:=Worksheets("Summary")
ActiveSheet.Name = NewSheet

Range("D2").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(DATE(2017,MONTH(DATEVALUE(MID(CELL(""filename"", RC[-5]), FIND(""]"", CELL(""filename"", RC[-5])) + 1, 255)&""1"")+1),1),0)"
Selection.NumberFormat = "m/d/yyyy"

Range("D3").Select
ActiveCell.FormulaR1C1 = "=MONTH(R[-1]C)"
Range("D3").Select
Selection.NumberFormat = "General"
MonthVal = ActiveCell.Value


Set CashWS = Sheets.Add
Sheets.Add.Name = "2017_0" & MonthVal & " Cash"


End Sub

Upvotes: 1

Views: 449

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

It's happening in the final step:

Set CashWS = Sheets.Add
Sheets.Add.Name = "2017_0" & MonthVal & " Cash"

Change that to:

Set CashWS = Sheets.Add
CashWS.Name = "2017_0" & MonthVal & " Cash"

Doing the second Sheet.Add.Name adds a sheet and renames it at the same time. When you do Set CashWS = Sheets.Add, Excel adds the sheet. So then, just use your WS Variable and update the name.

Edit: And for no real reason, I'll add that if you are partial to using Sheets.Add.Name... you could have done:

Sheets.Add.Name = "2017_0" & MonthVal & " Cash"
Set CashWS = ActiveSheet

then continued working with CashWS variable.

Upvotes: 6

Related Questions