Forum Discussion

mtschroe's avatar
mtschroe
Copper Contributor
Oct 01, 2021

How to create a new sheet with a name based on a cell? (Macro)

Hi All,

 

Trying to record a macro that will automatically refresh a report.  Because of the size of the dataset/number of formulas, each time the report is run I make a hard copy / hide the formulas.  

 

 

Everything seems to work but am stuck on the final step.  The sheet name of the hardcopy changes depending on the day- its run several times a month.  I have been trying to copy text from a cell and paste over the name of the new sheet.  Instead, the macro seems to paste whatever was entered the first time the macro was run.  Any way to make this dynamic?

 

 

excel
Macros and VBA

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 01, 2021

    mtschroe 

    If the sheet you want to name is the active sheet when you run the code, and you want to set its name to the value of cell A2 on the sheet:

     

    ActiveSheet.Name = Range("A2").Value

     

    If not: please provide more detailed information.

    • mtschroe's avatar
      mtschroe
      Copper Contributor
      Oct 01, 2021
      THanks so much for the fast response Hans!


      I did try this, however ran into a "1004 Error: Application-defined or Object-defined error"

      Any idea what might be causing this? When I click the debugger the last line is highlighted.

      Here is a snippet of the later part of the code:

      Range("A5:B5").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Sheets("Formulas").Select
      Range("B14").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("B13").Select
      Sheets("Formulas").Select
      Application.CutCopyMode = False
      Sheets("Formulas").Copy Before:=Sheets(7)
      Range("B13").Select
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Selection.End(xlToLeft).Select
      Range("B13").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("B13").Select
      Application.CutCopyMode = False
      Sheets("Update Report").Select
      Range("B3").Select
      Selection.Copy
      Sheets("Formulas (2)").Select
      ActiveSheet.Name = Range("A2").Value
      End Sub

Resources