Pasting Values in Excel not Formulas from a Macro

Question

I currently have a macro that copies data from one sheet and archives it in another sheet. The issue that I have is that it is copying and pasting the formulas and it need to copy and paste values only.

Sub Archive_Execution()

Dim mainworkbook As Workbook
Set mainworkbook = ActiveWorkbook
Dim rangeName
Dim strDataRange As Range
Dim keyRange As Range

rangeName = "Archive_Execution"
Application.Goto Reference:=rangeName
Selection.Copy
Sheets("Archive Execution").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
mainworkbook.Sheets("Archive Execution").Paste

Sheets("Archive Execution").Activate
Set strDataRange = Range("A2:AA1000000")
Set keyRange = Range("D1")
strDataRange.Sort Key1:=keyRange, Order1:=xlAscending

End Sub

Any help would be greatly appreciated on tweaking my code so that only values are pasted not formulas

Technically all you need to do is replace mainworkbook.Sheets("Archive Execution").Paste with mainworkbook.Sheets("Archive Execution").PasteSpecial xlPasteValues but I'd recommend rethinking your code to avoid using Select, Copy and Paste. Instead you can just set the cells in the new worksheet to the values you are importing. — Jordan
– Jordan, Commented Sep 27, 2016 at 9:32
Thank you for this. I have tried including .PasteSpecial xlPasteValues but I get an error of PasteSpecial method of Worksheet class failed? Please could you expand on what you mean by setting the cells in the new worksheet to the values? — Luke Turnbull
– Luke Turnbull, Commented Sep 27, 2016 at 9:40
Sorry, you would need to specify the range you are pasting to, at the moment you are only specifying the worksheet. Try: Sheets("Archive Execution").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues — Jordan
– Jordan, Commented Sep 27, 2016 at 9:48

score 2 · Accepted Answer · 2016-09-27 10:04:32Z

2

As mentioned in comments use Range().PasteSpecial xlPasteValues if you want to copy only values.

I also recommend using the Intersect method and Worksheets("").UsedRange to trim down your ranges.

Sub Archive_Execution()

    Dim strDataRange As Range
    Dim keyRange As Range

    With Sheets("Archive Execution")
        Range("Archive_Execution").Copy
        .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With

    With Sheets("Archive Execution")
        Set strDataRange = Intersect(.Range("A2:AA" & .Rows.Count), .UsedRange)
        Set keyRange = .Range("D1")
        strDataRange.Sort Key1:=keyRange, Order1:=xlAscending
    End With

End Sub

edited Sep 27, 2016 at 10:04

answered Sep 27, 2016 at 9:48

user6432984

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Turnbull Over a year ago

Thank you for your answer. This does copy and paste the values across but on the mainworkbook.Sheets("Archive Execution").Paste line, I get the error 'object required'

Tim Wilkinson · Accepted Answer · 2016-09-27 13:11:52Z

1

To avoid using Copy and Paste you can try something like the following, much faster than copy pasting.

Sub Archive_Execution()

    Dim mainworkbook As Workbook, rangeName
    Dim strDataRange As Range, keyRange As Range
    Dim r As Integer, c As Integer

    Set mainworkbook = ActiveWorkbook
    rangeName = "Archive_Execution"
    r = Range(rangeName).Rows.Count
    c = Range(rangeName).Columns.Count

    With Sheets("Archive Execution").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Resize(r, c) = Range(rangeName).Value
    End With

    Sheets("Archive Execution").Activate
    Set strDataRange = Range("A2:AA1000000")
    Set keyRange = Range("D1")
    strDataRange.Sort Key1:=keyRange, Order1:=xlAscending

End Sub

edited Sep 27, 2016 at 13:11

user6432984

answered Sep 27, 2016 at 9:53

Tim Wilkinson

3,79111 gold badges38 silver badges64 bronze badges

4 Comments

user6432984 Over a year ago

You will need to resize the range to fit the new data using that technique.

Tim Wilkinson Over a year ago

True, edited the answer as an alternative to copy and pasting.

user6432984 Over a year ago

Each range inside Sheets("Archive Execution").Range() needs to be qualified to reference Sheets("Archive Execution") and it would be cleaner to use resize. Would you mind if I edited your answer?

user6432984 Over a year ago

Technically I should have qualified Rows.Count to the worksheet but it won't cause an issue unless your working with an Excel 2003 workbook or older version from an Excel 2007 or later version workbook.

Collectives™ on Stack Overflow

Pasting Values in Excel not Formulas from a Macro

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related