1

I would like to have a Macro that generates a new tab and names that tab based on a cell value.

There is a tab called 'Dashboard'! on which the user enters information in assigned cells. Afterwards, I would like there to be a button that the user clicks, upon which the information from the 'Dashboard'! is copied to a new sheet. That new sheet should be given the name that the user has entered in cell B2 (the name of the day) and cell C2 (the date of that day), followed by the word "Training".

I have successfully recorded a Macro that does most of this, but naming the new tab based on a cell value seems impossible. I have manually changed the name of the tab, which shows up in the Macro perfectly. I do not know, however, how to make this name 'dynamic'.

Thanks in advance for any help, apologies in advance if this question is redundant and/or phrased inadequately.

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

@Rubén has suggested two good topics for you to review. However, both are slightly complicated by other issues (not relevant to your scenario), and "decoding" those aspects could well make the task more complicated than need be.

OTOH, I agree with Ruben that 1) you should use a trigger (or even a menu) to execute the code - that it up to you to investigate and resolve, and 2) you need to learn how to read and write data with scripts; macros are all very well, but they aren't a substitute in this scenario.

function wa13477102() {

    // set up the spreadsheet and establish the current sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

    // get the name of the current sheet - so that you don't accidentally try and name the new sheet with the same name.
  var oldName = sheet.getName();

    // get the values from Cell B2 and C2
    // Note for the date in Cell C2 we use "getDisplayValue()" for a WYSIWYG value.
  var myday = sheet.getRange("B2").getValue();
  var mydate = sheet.getRange("C2").getDisplayValue();

    // get the new name just by joining the two; there are lots of other ways to do this; you can search on Stackoverflow
  var newName = myday+mydate;

    // logic 
    // 1) if the length of the newname is grater than zero - that means there IS a value for newname; it's not just a blank.
    // AND
    // 2) the new name isn't the same as the old name.
  if (newName.toString().length>0 && newName !== oldName) {

    // insert a new sheet and name it with your newname.
    ss.insertSheet(newname);
  }
}
  • Thank you so much for your response and for taking the time out of your day to show me this. This helps a great deal. I will take the code you supplied and start analysing what it does and how to get it to work. Many, many thanks for the advice and support. – Othello Sep 23 at 6:47
  • If the answer was useful, then you might consider 'accepting' it. – Tedinoz Sep 29 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.