VBA Copy Sheet with all Formatting

[sswcharlie]

Hi
Have been doing some searching but came up with nothing like I want to do.

I want to copy a complete sheet with all data and formatting, column & row sizes etc. When the copy is made I want a messagebox so new name can be entered, and that will change the tab name.

A code I recorded goes part way only.

[VBA]
Sub Macro1()
'
' Macro1 Macro

Sheets("Sheet2").Select
Sheets("Sheet2").Copy Before:=Sheets(1)
Sheets("Sheet2 (2)").Select
Sheets("Sheet2 (2)").Name = "notes"
End Sub

[/VBA]

To confirm I need on line 2 to include all formatting in 'copy' and
in line 4 I need to be able to rename the new sheet with mssg asking for the new name.

Thankyou
Charlie Harris

[Jan Karel Pieterse]

Something like this:
[VBA]Sub CopyActiveSheet()
Dim sName As String
sName = InputBox("Please give new name for worksheet")
If Len(sName) > 0 Then
ActiveSheet.Copy after:=ActiveSheet
On Error Resume Next
ActiveSheet.Name = sName
If ActiveSheet.Name <> sName Then
MsgBox "Warning: an error occurred while renaming the new sheet; please check the sheetname"
End If
End If
End Sub
[/VBA]

[sswcharlie]

Hi Jan

Thanksfor that. I will put it to work.

Regards

Charlie Harris