Copying a Sheet with a New Name as Values Only

[Chonst]

So this is the code that I am using.

Sub SaveCount()   Dim sName As String
Dim i As Integer
i = ActiveWorkbook.Worksheets.Count


sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
If sName = "" Then Exit Sub
Worksheets(2).Copy after:=Worksheets(i)
ActiveSheet.Name = sName
End Sub

However I need the new worksheet to be values rather than formulas, so that edits to the original page do not affect this new sheet.

Can anyone help?

 

[Fluff]

Hi & welcome to the board.
Depending on your formulae, one option might be

Sub SaveCount()
   Dim sName As String
   Dim i As Long
   i = ActiveWorkbook.Worksheets.Count
   
   sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
   If sName = "" Then Exit Sub
   Worksheets(2).Copy after:=Worksheets(i)
   With ActiveSheet
      .Name = sName
      .UsedRange.Value = .UsedRange.Value
   End With
End Sub

 

[Johnny Thunder]

I would modify to something like

Sub SaveCount()   Dim sName As String
Dim i As Integer
i = ActiveWorkbook.Worksheets.Count


sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
If sName = "" Then Exit Sub
Worksheets(2).Copy after:=Worksheets(i)
ActiveSheet.Name = sName

With Sname
  .Cells.copy
     .Pastespecial xlvlaues
End with 


End Sub

So this is the code that I am using.

Sub SaveCount()   Dim sName As String
Dim i As Integer
i = ActiveWorkbook.Worksheets.Count


sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
If sName = "" Then Exit Sub
Worksheets(2).Copy after:=Worksheets(i)
ActiveSheet.Name = sName
End Sub

However I need the new worksheet to be values rather than formulas, so that edits to the original page do not affect this new sheet.

Can anyone help?

 

[Chonst]

Thank you! Happy to be here.

So I've tried that, but the problem has been that when I add the .UsedRange.Value part, everything pastes shifted around in the wrong spots. 1 row shifts over 13 columns and the rest of the sheet drops off.

 

[Chonst]

So this should work, but I'm getting the With-Object error that the object must be user defined, Object, or Variant.

Which is what I was running into with other With blocks.

I would modify to something like

Sub SaveCount()   Dim sName As String
Dim i As Integer
i = ActiveWorkbook.Worksheets.Count


sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
If sName = "" Then Exit Sub
Worksheets(2).Copy after:=Worksheets(i)
ActiveSheet.Name = sName

With Sname
  .Cells.copy
     .Pastespecial xlvlaues
End with 


End Sub

 

[Johnny Thunder]

I spelled Value wrong! in the with statement! LOL

Try that.

Sub SaveCount()   Dim sName As String
Dim i As Integer
i = ActiveWorkbook.Worksheets.Count




sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
If sName = "" Then Exit Sub
Worksheets(2).Copy after:=Worksheets(i)
ActiveSheet.Name = sName


With sheets("Sname")
  .Cells.copy
     .Pastespecial xlvalues
	 End with 




End Sub

 

[Chonst]

I'm so tired from today, I honestly looked over it 50 times, and didn't even notice.

So now the code is working, but I'm getting a Run-time error that the Subscript is out of range, I changed Sname to sName, and then Tried to redefine the index, but that freaked the entire thing out.

I appreciate your help though. You are getting me farther than I expected.

I spelled Value wrong! in the with statement! LOL

Try that.

Sub SaveCount()   Dim sName As String
Dim i As Integer
i = ActiveWorkbook.Worksheets.Count




sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
If sName = "" Then Exit Sub
Worksheets(2).Copy after:=Worksheets(i)
ActiveSheet.Name = sName


With sheets("Sname")
  .Cells.copy
     .Pastespecial xlvalues
     End with 




End Sub

 

[xenou]

Some further modifications below with my usual style of using plenty of validation on all-the-things-that-can-and-might-go-wrong.
but in the above post should the basic problem is in the With Sheets("sName") line, which should be amended to With Sheets(sName) to use the variable properly.

I think I prefer to create the sheet first, then hard-copy the values, on the premise that we don't need to have to formulas on the new sheet and copying them first might create a recalc (but I don't know - possibly and probably Excel is smart enough to know that these "new formulas" don't introduce any changed values so the difference might in fact be of no consequence).

Also, if we change the sheet name we should make sure it is a valid name. The way I have written this the code would still succeeds with the copying, but would fail to actually change the sheet name (so I guess I don't actually trust 99% of Excel users to always provide valid, non-duplicate sheet names, although they might do so 99% of the time).

Public Sub SaveCount()

Dim sName As String
Dim i As Integer
Dim wsOld As Worksheet
Dim wsNew As Worksheet

    Application.ScreenUpdating = False
    
    i = ActiveWorkbook.Worksheets.Count
    
    sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
    
    'No Name provided so user must have cancelled.
    If sName = "" Then
        Exit Sub
    End If
    
    'Name given but name is already being used for another worksheet
    If WorksheetExists(ActiveWorkbook, sName) Then
        MsgBox ("Error: Worksheet with name " & sName & " already exists.")
        Exit Sub
     End If
     
    'Sheet to copy is worksheets(2) (this is dangerous since it is hardcoded and worksheet order can be changed by users)
    Set wsOld = Worksheets(2)
     
    'Create New Sheet
    Set wsNew = Worksheets.Add(After:=Worksheets(i))
    
    'Hardcopy all values from old sheet to new sheet
    wsOld.Cells.Copy
    wsNew.Cells.PasteSpecial xlPasteValuesAndNumberFormats
    
    'Rename new worksheet (with error handling in case user has supplied an invalid name)
    On Error Resume Next
    wsNew.Name = sName
    wsNew.Cells(1, 1).Select 'make it pretty - otherwise whole sheet is still selected
    On Error GoTo 0

    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    
End Sub

Public Function WorksheetExists(ByRef wb As Workbook, ByVal wsName As String) As Boolean
Dim s As String

    WorksheetExists = False
    
    On Error GoTo ErrExit:
    s = wb.Worksheets(wsName)
    
    WorksheetExists = True
    
ErrExit:
End Function

 

[Chonst]

So this is just beautifully nice to look at,

When I ran it though, its throwing an error, then creating the new sheet.
-PasteSpecial method of Range class failed.

So is the VBA here creating a new sheet, and then just pasting the data into it?

Some further modifications below with my usual style of using plenty of validation on all-the-things-that-can-and-might-go-wrong.
but in the above post should the basic problem is in the With Sheets("sName") line, which should be amended to With Sheets(sName) to use the variable properly.

I think I prefer to create the sheet first, then hard-copy the values, on the premise that we don't need to have to formulas on the new sheet and copying them first might create a recalc (but I don't know - possibly and probably Excel is smart enough to know that these "new formulas" don't introduce any changed values so the difference might in fact be of no consequence).

Also, if we change the sheet name we should make sure it is a valid name. The way I have written this the code would still succeeds with the copying, but would fail to actually change the sheet name (so I guess I don't actually trust 99% of Excel users to always provide valid, non-duplicate sheet names, although they might do so 99% of the time).

Public Sub SaveCount()

Dim sName As String
Dim i As Integer
Dim wsOld As Worksheet
Dim wsNew As Worksheet

    Application.ScreenUpdating = False
    
    i = ActiveWorkbook.Worksheets.Count
    
    sName = Application.InputBox("Enter a name for the new sheet", "Add sheet")
    
    'No Name provided so user must have cancelled.
    If sName = "" Then
        Exit Sub
    End If
    
    'Name given but name is already being used for another worksheet
    If WorksheetExists(ActiveWorkbook, sName) Then
        MsgBox ("Error: Worksheet with name " & sName & " already exists.")
        Exit Sub
     End If
     
    'Sheet to copy is worksheets(2) (this is dangerous since it is hardcoded and worksheet order can be changed by users)
    Set wsOld = Worksheets(2)
     
    'Create New Sheet
    Set wsNew = Worksheets.Add(After:=Worksheets(i))
    
    'Hardcopy all values from old sheet to new sheet
    wsOld.Cells.Copy
    wsNew.Cells.PasteSpecial xlPasteValuesAndNumberFormats
    
    'Rename new worksheet (with error handling in case user has supplied an invalid name)
    On Error Resume Next
    wsNew.Name = sName
    wsNew.Cells(1, 1).Select 'make it pretty - otherwise whole sheet is still selected
    On Error GoTo 0

    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    
End Sub

Public Function WorksheetExists(ByRef wb As Workbook, ByVal wsName As String) As Boolean
Dim s As String

    WorksheetExists = False
    
    On Error GoTo ErrExit:
    s = wb.Worksheets(wsName)
    
    WorksheetExists = True
    
ErrExit:
End Function

 

[Chonst]

So for those interested,
Here is what I ended up with.

Thank you to every one who helped.

Special Thanks

Sub SaveCount2()Dim sName As String
Dim i As Integer
Dim wsOld As Worksheet
Dim wsNew As Worksheet
Dim rg As Range
Dim uv As UniqueValues


    Application.ScreenUpdating = False
    
    i = ActiveWorkbook.Worksheets.Count
    
    sName = Application.InputBox("Enter Date", "Save Count")
    If sName = "" Then
        Exit Sub
    End If
    


    If WorksheetExists(ActiveWorkbook, sName) Then
        MsgBox ("Error: Worksheet with name " & sName & " already exists.")
        Exit Sub
     End If
     
    Set wsOld = Worksheets("Count")
    Set wsNew = Worksheets.Add(After:=Worksheets(i))
    
    wsOld.Range("E4:L1005").SpecialCells(xlCellTypeVisible).Copy
    wsNew.Range("B2:F1005").PasteSpecial xlPasteColumnWidths
    wsNew.Range("B2:F1005").SpecialCells(xlCellTypeVisible).PasteSpecial xlPasteValues
    
    Set rg = Range("B2:B1005", Range("F2:F1005").End(xlDown))
    Set uv = rg.FormatConditions.AddUniqueValues
    
    uv.DupeUnique = xlDuplicate
    uv.Interior.ColorIndex = 35
    
    wsNew.Range("C2:C1005").ClearFormats
    
    wsNew.Range("B2:F2").Font.FontStyle = "Bold"
    wsNew.Range("B2:F2").Font.FontStyle = "Italic"
    
    On Error Resume Next
    wsNew.Name = sName
    wsNew.Cells(1, 1).Select
    On Error GoTo 0


    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    
End Sub


Public Function WorksheetExists(ByRef wb As Workbook, ByVal wsName As String) As Boolean
Dim s As String


    WorksheetExists = False
    
    On Error GoTo ErrExit:
    s = wb.Worksheets(wsName)
    
    WorksheetExists = True
    
ErrExit:
End Function