coping a entire tab to a new tab

[Vimal Lalla]

Hi

I am new to excel, I have created a master worksheet, i want to copy that
entire worksheet with its formatting to a new worksheet with a different
name, can anyone help me out

 

[paul.robinson]

Hi

I am new to excel, I have created a master worksheet, i want to copy that
entire worksheet with its formatting to a new worksheet with a different
name, can anyone help me out

Hi
Right click the worksheet tab at the bottom. You will see a menu item
for Move or Copy...Click that and in the dialog box click on (move to
end) and click the box for Create Copy. Now right click the worksheet
tab for the copied sheet and click Rename - then type the new name.
I'm assuming by new that you really mean new, and don't want this done
using VBA.
regards
Paul

 

[Vimal Lalla]

Hi

thanks for your reply, i need it done using vba

 

[john]

Add following to standard vba module.
Code should copy activesheet & rename it to that entered in the inputbox.
If duplicate name found an error will be reported.

Hope helpful

Sub CopySheet()

Dim ShName As Variant
TOP:
ShName = Application.InputBox(prompt:="Enter New Name", Title:="Rename
Worksheet", Type:=2)
If VarType(ShName) = vbBoolean Then
If ShName = False Then
Debug.Print "cancelled"
Exit Sub
End If
End If
On Error Resume Next
If Worksheets(ShName) Is Nothing Then
With ActiveSheet
.Copy after:=Sheets(.Index)
End With
ActiveSheet.Name = StrConv(ShName, vbProperCase)
Else
MsgBox ShName & " Sheet Already Exists"
GoTo TOP
End If
End Sub

 

[Vimal Lalla]

Thanks John for all your help
That works.

Add following to standard vba module.
Code should copy activesheet & rename it to that entered in the inputbox.
If duplicate name found an error will be reported.

Hope helpful

Sub CopySheet()

Dim ShName As Variant
TOP:
ShName = Application.InputBox(prompt:="Enter New Name", Title:="Rename
Worksheet", Type:=2)
If VarType(ShName) = vbBoolean Then
If ShName = False Then
Debug.Print "cancelled"
Exit Sub
End If
End If
On Error Resume Next
If Worksheets(ShName) Is Nothing Then
With ActiveSheet
.Copy after:=Sheets(.Index)
End With
ActiveSheet.Name = StrConv(ShName, vbProperCase)
Else
MsgBox ShName & " Sheet Already Exists"
GoTo TOP
End If
End Sub

 

[Vimal Lalla]

i have placed a button on the master page, when i click the button, it
creates the new sheet as i want it to, but it also copies the button, is
there a way to exclude that.

 

[paul.robinson]

i have placed a button on the master page, when i click the button, it
creates the new sheet as i want it to, but it also copies the button, is
there a way to exclude that.






- Show quoted text -

Hi
Dim SheetButton as Button

for each SheetButton in ActiveSheet.Buttons
SheetButton.delete
next SheetButton

insert the loop after the copy
regards
Paul

 

[john]

Paul's solution perfectly ok - but if you are only adding 1 button to master
sheet you only need simple change to code:

Sub CopySheet()

Dim ShName As Variant
TOP:
ShName = Application.InputBox(prompt:="Enter New Name", Title:="Rename
Worksheet", Type:=2)
If VarType(ShName) = vbBoolean Then
If ShName = False Then
Debug.Print "cancelled"
Exit Sub
End If
End If
On Error Resume Next
If Worksheets(ShName) Is Nothing Then
With ActiveSheet
.Copy after:=Sheets(.Index)
End With
With ActiveSheet
.Name = StrConv(ShName, vbProperCase)
.Buttons(1).Delete
End With
Else
MsgBox ShName & " Sheet Already Exists"
GoTo TOP
End If
End Sub

Hope helpful