Rename tab with cell reference

[Doedtman]

Hello! I'm trying to create a code that will copy and paste information to a new tab and rename the tab using the date that's in cell A2. The date will change every month and is in the format 'March 2011'. Here's what I have so far:

Sub NewTab()
Sheets("List").Select
Cells.Select
Selection.Copy
Dim ws As Worksheet
Set ws = Sheets.Add
ws.Name = "Temp"
Sheets("Temp").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A2:E2").Select
Selection.UnMerge
Range("A2").Select
Sheets("Temp").Name = Range("a2")
Range("A1").Select
End Sub

This probably isn't the best code to use, but it works as long as I have anything in cell A2, except a date. For some reason a date causes an error. I've also considered using a code that just names the tab with the prior month date. Example: In March, rename the tab February 2011. But I'm not sure how to do that. I'd really appreciate any help.

Thanks!
Jodi

 

[MrKowz]

Try:

Sub NewTab()
Dim ws As Worksheet
Sheets("List").Cells.Copy
Set ws = Sheets.Add
ws.Name = Sheets("List").Range("A2").Text
With ws.Range("A1")
    .PasteSpecial Paste:=xlValues
    .PasteSpecial Paste:=xlPasteFormats
End With
End Sub

 

[Doedtman]

That's works great! Thank you so much! Is there any way to have the new tab created after a specific tab, or can they only be added to the beginning or end?

 

[MrKowz]

Try:

Sub NewTab()
Dim ws As Worksheet
Sheets("List").Cells.Copy
Sheets.Add After:=Sheets("List")
Set ws = ActiveSheet
ws.Name = Sheets("List").Range("A2").Text
With ws.Range("A1")
    .PasteSpecial Paste:=xlValues
    .PasteSpecial Paste:=xlPasteFormats
End With
End Sub

 

[Doedtman]

That works but it creates a blank sheet after it. Is there a way to get rid of this blank sheet?

 

[MrKowz]

It works fine on my end. It creates a new sheet after the "List" sheet, renames it to whatever is in A2 on the "List" sheet, and then copies/pastes everything from "List".

Is there any other code you are running?

 

[Doedtman]

Here's the entire code I'm using. Maybe it's the last part that is creating a problem.

Private Sub Reset_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
emptyUnlocked
NewTab
DeleteTabs2
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub emptyUnlocked()
ActiveSheet.Protect
On Error Resume Next
ActiveSheet.UsedRange = ""
On Error GoTo 0
ActiveSheet.Unprotect
ActiveSheet.Protect
End Sub

Sub NewTab()
Dim ws As Worksheet
Sheets("List").Cells.Copy
Sheets.Add after:=Sheets("Dr. List")
Set ws = Sheets.Add
ws.Name = Sheets("List").Range("A2").Text
With ws.Range("A1")
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlPasteFormats
End With

End Sub

Sub DeleteTabs2()
Dim wsht As Worksheet
Dim wksht As Worksheet

On Error Resume Next
Set wsht = Sheets("Journal Entry")
On Error GoTo 0
Range("A1").Select
If Not wsht Is Nothing Then
wsht.Delete
Else
Exit Sub
End If

On Error Resume Next
Set wksht = Sheets("List")
On Error GoTo 0
Range("A1").Select
If Not wsht Is Nothing Then
wksht.Delete
Else
Exit Sub
End If


End Sub

 

[MrKowz]

Your NewTab() code was wrong, use:

Sub NewTab()
Dim ws As Worksheet
Sheets("List").Cells.Copy
Sheets.Add after:=Sheets("Dr. List")
Set ws =[B][COLOR=red] ActiveSheet[/COLOR][/B]
ws.Name = Sheets("List").Range("A2").Text
With ws.Range("A1")
    .PasteSpecial Paste:=xlValues
    .PasteSpecial Paste:=xlPasteFormats
End With
End Sub

Whenever a sheet is added to the workbook, it becomes the ActiveSheet.

 

[Doedtman]

I'm sorry! All that for a typing error! It works perfect now. Thank you for your help!

Jodi