This will copy sheet 3 to the end of the workbook and name it: Sheet3(2)
The copy paste method is real sensitive, every time I work with it I am forced to play with it. Even if I copy it from a workbook that works now, the data or situation in the new workbook may give me that: Type Mismatch Error 13.
Most of the time it is fixed by correcting the way the data is selected, that is if a Set is used or a Var.
This may help?
Sub myNewData()
'Standard Module code, Like: Module1.
'Ask for new sheet name and copy all data from MyData to the new sheet.
Dim Message$, Title$, Default$, MySheet$, MyList$, MyTest
Dim Message2$, Title2$, Default2$, MyData$
MyTest = False
MyData = "Sheet1"
'Message, title, and default value.
Message = "Enter a New ""Sheet Name"" to add to this workBook:" ' Set prompt.
Title = "Get Sheet Name!" ' Set title.
Default = "TestSheet" ' Set default.
'Get New Sheet Name.
MySheet = InputBox(Message, Title, Default)
'This adds a sheet and names it "your name" or go's to the sheet inputed if it exists.
'Get all sheets name and test for new sheet name.
For Each ws In Worksheets
If ws.Name = MySheet Then
MyTest = True
End If
Next ws
If MyTest <> True Then
Sheets.Add.Name = MySheet
End If
'This selects your new sheet and moves it after sheet "MyData," which could be any sheet name.
Sheets(MySheet).Select
Sheets(MySheet).Move After:=Sheets(MyData)
'This selects the sheet with the data and its range.
'Message, title, and default value.
Message2 = "Enter the Sheet name to get your data from:" ' Set prompt.
Title2 = "Get Data Sheet Name!" ' Set title.
Default2 = "Sheet1" ' Set default.
'Get Data Sheet Name.
MyData = InputBox(Message2, Title2, Default2)
Sheets(MyData).Select
Sheets(MyData).Range("A1").Select
Sheets(MyData).Range(Range("A1"), Sheets(MyData).Range("A65536").End(xlUp)).Select
'This will copy and paste the data to your new sheet.
Selection.Copy
Sheets(MyData).Select
Sheets(MyData).Range("A1").Select
'Test for existing Data on copy to sheet.
If Sheets(MySheet).Range("A1").Value <> "" Then
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
Sheets(MySheet).Range("A65536").End(xlUp).Offset(1, 0).Select
Else
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
End If
'Paste data from MyData to your new sheet.
ActiveSheet.Paste
'At this point your data will be on the new sheet and selected for the next step.
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
End Sub