VBA Create Copy Paste Values of single worksheet

[Bembridge7]

Hi Guys

I am hoping someone can help me with this..So I am trying to create a copy of one worksheet and paste special values. I'd like to be able to choose a name for the worksheet and keep the worksheet within the existing workbook, moving the worksheet to the front. Apoligies if the format is not in line with requirements, this is my first post. I'm pretty sure this part is not correct for some reason - " Before:=ActiveWorkbook.Sheets(1)"

Here is my code:

Public Sub CopySheetToReport()
Dim newName As String

On Error Resume Next
newName = InputBox("Enter the name for the copied worksheet")

If newName <> "" Then
ActiveSheet.Copy.PasteSSpecial Paste:=xlPasteValues
Before:=ActiveWorkbook.Sheets(1)
On Error Resume Next
ActiveSheet.Name = newName
End If
End Sub

Any help would be much appreciated

 

[mohadin]

Public Sub CopySheetToReport()
    Dim newName As String
    newName = InputBox("Enter the name for the copied worksheet")
    Application.ScreenUpdating = False
    If newName <> "" Then
        ActiveSheet.Copy Before:=ActiveSheet
        ActiveSheet.Name = newName
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Bembridge7]

Thank you Mohadin for coming back to me so quickly. The copy works perfectly, however I noticed the copy still contains formulae and is not paste valued but not sure why? Also, the worksheet appears before the active sheet and I am trying to move the coped sheet to the start of the workbook so it appears as the first worksheet once the macro has run. Is this the correct code to add in to ensure the copied worksheet is moved to the start:

ActiveSheet.Copy Before:=Worksheets("Sheet1")

Thanks again!

Public Sub CopySheetToReport()
    Dim newName As String
    newName = InputBox("Enter the name for the copied worksheet")
    Application.ScreenUpdating = False
    If newName <> "" Then
        ActiveSheet.Copy Before:=ActiveSheet
        ActiveSheet.Name = newName
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[mohadin]

Hi
For pasting values , it works fine here!!

[COLOR=#333333]ActiveSheet.Copy Before:=Worksheets("Sheet1")[/COLOR]

It will do the job only for the first time when sheet1 is the first one
so try

ActiveSheet.Copy Before:=Worksheets(1)