Paste as Values

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
712
Code:
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
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,426
Try:
Code:
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

Well-known Member
Joined
Apr 20, 2009
Messages
712
Try:
Code:
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

Well-known Member
Joined
Apr 20, 2009
Messages
712
Code:
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

Active Member
Joined
Feb 17, 2002
Messages
264
copy range (bypasses the clipboard)
Code:
Sheet2.Range("A1:A200").Copy Destination:=Sheet1.Range("A1")
if only values are needed...
Code:
Sheet1.Range("A1:A200").Value2 = Sheet2.Range("A1:A200").Value2
'if only formulas are needed...
Code:
Sheet1.Range("A1:A200").Formula = Sheet2.Range("A1:A200").Formula
if using an unknown range...
Code:
'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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,311
Office Version
365
Platform
Windows
How about
Code:
    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
 

Forum statistics

Threads
1,077,976
Messages
5,337,499
Members
399,153
Latest member
Tsmith25

Some videos you may like

This Week's Hot Topics

Top