Macro to insert the date in the tab

[ladybirdwalker]

Hi

i have tried to insert the date (today) as the tab name using a formula but will not allow me to use /.... how can i get the macro to allow me to insert the date into the tab name

Range("A1").Select
Range("A1").Value = Date
Selection.NumberFormat = "[$-809]dd mmmm yyyy;@"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B12").Select
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub

 

[lenze]

Hi and welcome to the board!!
I don't really see what you are trying to do. What would be an example Tab name? Where does the Copy paste come into play? Why are you selecting B12?

lenze

 

[njimack]

I also don't see how the code you posted relates to your question, but perhaps something like this:

Sub Test()
ActiveSheet.Name = Format(Date, "dd-mm-yy")
End Sub

 

[ladybirdwalker]

Sorry should have been clearer I want to change the tab name to today's date everytime i run it. i have a few other things going on in the macro so only included part of it... I got the B12 section and the bits before it on another website about checking A1 and putting it in the tab if correct..... I should say this is my first macro ever!

here is the whole macro
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 23/11/2009 by TNS
'
' Keyboard Shortcut: Ctrl+q
'
Range("1:2,K:K").Select
Range("K1").Activate
Selection.Font.Bold = True
Columns("C:D").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Range("L3").Select
ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Range("L3").Select
Selection.AutoFill Destination:=Range("L3:L219"), Type:=xlFillDefault
Range("L3:L219").Select
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("K2").Select
Selection.Copy
Range("L2").Select
ActiveSheet.Paste
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Columns("A:S").Select
Selection.ColumnWidth = 12.71
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range("A1").Value = Date
Selection.NumberFormat = "[$-809]dd mmmm yyyy;@"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B12").Select
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub

 

[pujo]

Sub Test()
ActiveSheet.Name = Format(Date, "dd-mm-yy")
End Sub

How to combine these two lines of code?

ActiveSheet.Name = InputBox("Please Enter Historical Name For This Report. Example: January 2010 1-15")

And

ActiveSheet.Name = Format(Date, "dd-mm-yy")

To where the user add the name and the code tags it with the correct date. Is this possible?

Thanks,
Pujo