首页 文章

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

提问于
浏览 433
0

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

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

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 回答

  • 0

    您可以创建一个子例程并将快捷键设置为“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
    
  • 0

    你可以这样做:

    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
    

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

  • 0

    尝试

    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
    

相关问题