Paste as Values

[Guzzlr]

Set Template = Sheets("Template")
    Template.Select
    Template.Copy After:=Template
    Sheets("Template (2)").Name = "ABL"

Hello all,
The code above is working to copy one sheet and move to a new sheet. However, I want to paste into the new sheet as values. I'm not sure how to do that.
Can someone help please?
Thanks for the help

 

[mumps]

Try:

Sub PasteValues()
    Dim Template As Worksheet
    Set Template = Sheets("Template")
    Template.Copy After:=Template
    With ActiveSheet
        .Name = "ABL"
        .UsedRange.Cells.Value = .UsedRange.Cells.Value
    End With
End Sub

 

[Guzzlr]

Try:

Sub PasteValues()
    Dim Template As Worksheet
    Set Template = Sheets("Template")
    Template.Copy After:=Template
    With ActiveSheet
        .Name = "ABL"
        .UsedRange.Cells.Value = .UsedRange.Cells.Value
    End With
End Sub

That did some really funky stuff like giving me a warning of "cant execute code in break mode" also seemed to combine columns together. The reason I'm trying to paste as values, is so I can take away the filters that are copied then pasted, with the filters still in place.

 

[Guzzlr]

Set Template = Sheets("Template")
Template.Select
    Range("A8:AI1991").Select
    Selection.Copy
    Sheets.Add After:=Template
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
With ActiveSheet
.Name = "ABL"
End With

This is working, but I would like to clean it up...it's using the recorder so it probably has a lot of not-needed code.
Thanks for the help

 

[JugglerJAF]

copy range (bypasses the clipboard)

Sheet2.Range("A1:A200").Copy Destination:=Sheet1.Range("A1")

if only values are needed...

Sheet1.Range("A1:A200").Value2 = Sheet2.Range("A1:A200").Value2

'if only formulas are needed...

Sheet1.Range("A1:A200").Formula = Sheet2.Range("A1:A200").Formula

if using an unknown range...

'select sheet with data to be copied
    Sheet02.Select
'set variable based on current region
    my_copy_range = Range("A1").CurrentRegion.Address
'copy values to same region on other workbook from source workbook
    Sheet04.Range(my_copy_range).Value2 = Sheet02.Range(my_copy_range).Value2

 

[Fluff]

How about

    Dim Addr As String
    With Sheets("template")
        Addr = .UsedRange.Address
        Sheets.Add(, Sheets(.Index)).Name = "ABL"
        Sheets("ABL").Range(Addr).Value2 = .UsedRange.Value2
    End With