在Excel中粘贴值而不是宏中的公式

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

在调整我的代码时,将非常感谢任何帮助,以便只粘贴值而不是公式

2 个解决方案

#1

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

如注释中所述,如果您只想复制值,请使用Range()。PasteSpecial xlPasteValues。

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

我还建议使用Intersect方法和Worksheets(“”)。UsedRange来减少范围。

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

#2

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

#1