Paste as Values

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
943
Office Version
  1. 2016
Platform
  1. Windows
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:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top