PDA

View Full Version : VBA Copy Sheet with all Formatting



sswcharlie
05-23-2011, 08:49 PM
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.


Sub Macro1()
'
' Macro1 Macro

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



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
05-23-2011, 11:53 PM
Something like this:
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

sswcharlie
05-24-2011, 12:26 AM
Hi Jan

Thanksfor that. I will put it to work.

Regards

Charlie Harris