VBA - new sheet with dinamic name

[catalincirjan]

Hello,

I used the recording option to create a script that will copy the content of “Tracks” sheet and paste it into a new sheet.
I would like to give the name of the newly created sheet with the current date (October 30th – if possible / October 30)

I tried some searched solutions on the web, put they don’t seem to work … as if the syntax is not the same ….

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Format(Date, "DD-MM-YY")

It doesn t even recongnize .Name = ….
Not sure if a reason might be the fact that I am doing this on a file uploaded on sharepoint directly ... I know we have Office 365 pro plus or smth like that


Could you please help?



(first time using vba, sorry)

 

[mohadin]

Hi
Try some thing like

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Format(Date, "DD_MM_YY")

Excel Do not "-" or special character in sheet name

 

[Peter_SSs]

Excel Do not "-" or special character in sheet name

Whilst there are some characters not allowed, dashes are fine in sheet names.

 

[Peter_SSs]

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Format(Date, "DD-MM-YY")

That code works fine for me. What happens if you use it?

., or is it just that you want a slightly different date format like this?

ActiveSheet.Name = Format(Date, "mmmm dd")

or this

ActiveSheet.Name = Format(Date, "mmmm d") & Evaluate("mid(""thstndrdth"",min(9,2*right(" & Day(Date) & ")*(mod(" & Day(Date) & "-11,100)>2)+1),2)"

Note, too that if you want you can add the sheet and name it in the same code line. For example

Sheets.Add(After:=ActiveSheet).Name = Format(Date, "mmmm dd")

 

[catalincirjan]

Maybe I'm not putting it in the right place in the code ( see in photo )
i was adding exactly at the end, instead of sheet1.setname ....

I tried sheet1.Name = ... -> it doesn't seem to recognize 'sheet1.Name =' as correct code syntax ...

 

[catalincirjan]

I did try out the following code on local copy of the file

Sub Snapshot()
' Snapshot Macro


Sheets("Tracks").Select
Cells.Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Show original message



End Sub



And it worked like a charm
When I upload this to SharePoint, I can’t even find this macro.
I’ve copy pasted in a script created with recording, but it didn’t work (27 errors) -

My conclusion is that the coding syntax seems different on the online version.
This is what I have …. If you can see it differs very much from the above solution.

function main(workbook: ExcelScript.Workbook) {
// Add a new worksheet
let sheet1 = workbook.addWorksheet();
let tracks = workbook.getWorksheet("Tracks");
// Paste range at Sheet1!A1 from Tracks!1:1048576
sheet1.getRange("A1")
.copyFrom(tracks.getRange("1:1048576"), ExcelScript.RangeCopyType.all, false, false);
// Rename worksheet to "November 4th"
sheet1.setName("November 4th"

 

[catalincirjan]

Can the same be done with the other syntax format?