-
VBA Copy Sheet with all Formatting
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
-
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]
-
Hi Jan
Thanksfor that. I will put it to work.
Regards
Charlie Harris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules