Paste as Values

Guzzlr

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

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,147
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
755
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
755
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
269
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
42,353
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,784
Messages
5,482,906
Members
407,368
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top