0

I am trying to create a new sheet with a custom name for that sheet that takes in an input. For instance, I enter a date (June 1, 2020) into "sheet1" and the output is a new sheet title "June 1, 2020.

My current code:

Dim sheetName As String
Sheets("Input").Select
Range("C2").Select
sheetName = Selection.Copy

Sheets.Add After:=activeSheet
activeSheet.Name = "Week of " & sheetName

This returns a new sheet with the name "Week of True". For some reason the variable is stored as "True", rather than the inputted date.

Any help would be greatly appreciated, Thank you!

MattMcMahon25
  • 1
  • 1

2 Answers2

2

As BigBen wrote in the comments, in VBA you simply access the content of a cell using Value. Often you can see that something like Sheets("Input").Range("C2") is used, omitting Value. That is basically doing the same, but my advice is to add the .Value for clarity.

To explain the "Week of True" you get: Copy (and it's counterpart Paste) is used to copy and paste content, similar to use Ctrl+C and Ctrl+V in Excel. The method Copy does not return the content of whatever was copied, it writes it into something that is often named the "Clipboard". It returns True if this writing (copying) was successfull - and this True is copied into your variable sheetName.

A rather obligatory hint: You usually don't need to use Select in VBA and should avoid it. Have a look to How to avoid using Select in Excel VBA

FunThomas
  • 23,043
  • 3
  • 18
  • 34
1

Like this:

Dim wb As Workbook, ws As Worksheet, wsInput

Set wb = ThisWorkbook
Set wsInput = wb.Sheets("Input")
Set ws = wb.Sheets.Add(After:=wsInput)

ws.Name = Format(wsInput.Range("C2").Value, "mmmm d, yyyy")

No need for select/activate

Tim Williams
  • 154,628
  • 8
  • 97
  • 125