1

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
Ritu Mishra
  • 83
  • 2
  • 12
  • In addition to the answer below, it's worth mentioning that it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). It will help shorten/"tighten up" your code. – BruceWayne Jun 15 '17 at 21:42

1 Answers1

6

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110