只复制单元格的值，而不是粘贴-Java 学习之路

我正在尝试创建一个命令按钮，该按钮将运行脚本以仅复制单元格的值 . 截至目前，我有一个宏，将单元格设置为公式 . 当我复制单元格时，它想要复制公式，而不是值 . 我希望此按钮仅将范围的值复制到剪贴板以复制到其他工作表 . 这是我到目前为止的代码 .

这部分代码用于表示单元格 .

Dim LookupRange As Range
Dim c As Variant

Application.ScreenUpdating = False
Set LookupRange = Range("C9:C300") ' Set range in Column B

For Each c In LookupRange 'Loop through range
    If c.Value <> "" Then 'If value in B is not empty then

     Cells(c.Row, 15).FormulaR1C1 = "=""""&RC[-11]&"" ""&RC[-6]&"" (MK NO. 
     ""&RC[-13]&"")"""

     Cells(c.Row, 14).FormulaR1C1 = "=""""&RC[-11]&"""""


     End If
Next c
Application.ScreenUpdating = True

这部分代码仅用于复制这些单元格的值 .

Sub CommandButton_CopyNumbers()

Dim LastR As Long

'FIND LAST ROW OF DATA IN COLUMN N
LastR = Cells(Rows.Count, 14).End(xlUp).Row


Range("K9:N &LastR").Select 'This part is not working
Selection.Copy

End Sub

第一个代码工作正常，但是有没有办法将这些单元格设置为等于值而不是公式并将它们复制到剪贴板？

3 回答

您可以创建一个子例程并将快捷键设置为“ctrl v”，模拟默认的粘贴快捷方式 .

这样，我们将使用新过程替换默认的粘贴快捷方式，该过程将仅粘贴复制的选择的值 .

Sub PasteAsValues()
'Set the shortcut to "ctrl + v"

Selection.PasteSpecial xlPasteValues

End Sub

正确的代码：

Sub CommandButton_CopyNumbers()

Dim LastR As Long

'FIND LAST ROW OF DATA IN COLUMN N
LastR = Cells(Rows.Count, 14).End(xlUp).Row

Range("K9:N" & LastR).Select 'This part is not working
Selection.Copy

End Sub

回复于 2024-04-20T00:33:08+08:00

你可以这样做：

CopyOnlyValues Range("K9:N" & LastR)

子要复制范围为文本：

Sub CopyOnlyValues(rng As Range)
    'requires project reference to "Microsoft Forms 2.0 Object Library"
    Dim txt, rw As Range, objDat As New DataObject
    Dim c As Range, sepV As String, sepL

    sepL = ""
    For Each rw In rng.Rows
        sepV = ""
        For Each c In rw.Cells
            txt = txt & sepV & c.Value
            sepV = vbTab
        Next c
        txt = txt & vbLf
    Next rw

    objDat.SetText txt
    objDat.PutInClipboard
End Sub

注意：在很大的范围内，性能可能不是很好 .

回复于 2024-04-20T00:33:08+08:00

尝试

Sub CommandButton_CopyNumbers()

    Dim LastR As Long
    Dim vDB As Variant
    Dim Target As Range
    Dim Ws As Worksheet
    'FIND LAST ROW OF DATA IN COLUMN N
    LastR = Cells(Rows.Count, 14).End(xlUp).Row

    Set Ws = Sheets("Different Worksheet name")

    vDB = Range("K9:N" & LastR)

    Set Target = Ws.Range("a1") 'set your cell one
    Target.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB

End Sub

回复于 2024-04-20T00:33:08+08:00

只复制单元格的值，而不是粘贴

3 回答

相关问题