Forum Discussion
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?
5 Replies
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.
- 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 SubWhich sheet contains the cell with the name?
