复制，但仅粘贴值? [英] Copy, But Paste only the values?

问题描述

我有一个宏可以根据过滤器复制数据.但是要复制的某些单元格中已经有公式，因此我想复制该单元格并仅将其粘贴为文本或值.我尝试使用以下内容.我尝试了xlValues和Format文本，它们都给出了错误-运行时错误'438'.对象不支持此属性或方法.

I have a macro to copy data based on filters. But some cells that are being copied from already have formulas in them, so I want to copy that and paste only as text or values.I tried using the below. I tried xlValues and Format text, both of them giving errors - Run Time error '438' . Object doesn't support this property or method.

Sub DS()

    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet

    Dim sourceWorkbookPath As String
    Dim targetWorkbookPath As String
    Dim lastRow As Long



    ' Define workbooks paths
    sourceWorkbookPath = "Exceptional Transfer -2020 v2.xlsm"
    targetWorkbookPath = "template2.xlsx"

    ' Set a reference to the target Workbook and sheets
    Set sourceWorkbook = Workbooks.Open(sourceWorkbookPath)
    Set targetWorkbook = Workbooks.Open(targetWorkbookPath)

    ' definr worksheet's names for each workbook
    Set sourceSheet = sourceWorkbook.Worksheets("A")
    Set targetSheet = targetWorkbook.Worksheets("B")



    With sourceSheet

        ' Get last row
         lastRow = .Range("K" & .Rows.Count).End(xlUp).Row

        .Range("A1:Q1").AutoFilter Field:=14, Criteria1:="PENDING"
        .Range("A1:Q1").AutoFilter Field:=11, Criteria1:="U3R", Operator:=xlOr, Criteria2:="U2R"

        .Range("K2:K" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("A1")
        .Range("C2:C" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("B1")
        .Range("E2:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("E1").PasteSpecial xlValues
        .Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("F1")
        .Range("S2:S" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("I1")
        .Range("T2:T" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("J1")
        .Range("U2:U" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("C1")



    End With

    On Error Resume Next
    sourceSheet.ShowAllData
    On Error GoTo 0

End Sub

推荐答案

使用 Destination 拖放单行复制/粘贴，然后切换到多行复制/粘贴方法，该方法可让您访问到 .PasteSpecial 属性

Drop the one-liner copy/paste with Destination and switch over to the multi-line copy/paste method which gives you access to the .PasteSpecial property

'Copy here
.Range("K2:K" & lastrow).SpecialCells(xlCellTypeVisible).Copy

'Paste here
targetSheet.Range("A1").PasteSpecial xlPasteValues

这篇关于复制，但仅粘贴值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！