VBA - new sheet with dinamic name

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Attachments

  • image001 (5).png
    image001 (5).png
    20 KB · Views: 4

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Try some thing like
VBA Code:
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Format(Date, "DD_MM_YY")
Excel Do not "-" or special character in sheet name
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,478
Office Version
  1. 365
Platform
  1. Windows
Excel Do not "-" or special character in sheet name
Whilst there are some characters not allowed, dashes are fine in sheet names.

1604299603033.png
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,478
Office Version
  1. 365
Platform
  1. Windows
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?
VBA Code:
ActiveSheet.Name = Format(Date, "mmmm dd")

or this
VBA Code:
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
VBA Code:
Sheets.Add(After:=ActiveSheet).Name = Format(Date, "mmmm dd")
 
Last edited:

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,573
Members
416,925
Latest member
malamutus

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