首页 文章

Excel将一系列单元格值复制到剪贴板

提问于
浏览
1

我想将一系列单元格(仅值/文本)复制到剪贴板,这样用户只有在将它们粘贴到另一个电子表格时才必须粘贴特殊值 .

这是我到目前为止:

Private Sub CommandButton1_Click()
    With New DataObject
        .SetText Range("A32:Q32").Text
        .PutInClipboard
    End With
    'Range("A32:Q32").Copy
End Sub

这给了我一个运行时错误

94无效使用Null

如果我只是使用注释掉的代码 Range.("A32:Q32").Copy 它会复制公式,除非用户进行特殊粘贴,否则它们会产生各种参考错误 .

2 回答

  • 2

    我不知道dataobject,所以我提出了一个解决方法,让用户也选择目标单元格

    Private Sub CommandButton1_Click()
        Dim userRng As Range
        With ActiveSheet 'reference currently active sheet, before the user could change it via inputbox 
            Set userRange = GetUserRange()
            If Not userRange Is Nothing Then ' if the user chose a valid range
                With .Range("A32:Q32")
                    userRange.Resize(.Rows.Count, .Columns.Count).Value =.Value ' paste values only
                End With
            End If
        End With
    End Sub
    
    
    Function GetUserRange() As Range   
    ' adapted from http://spreadsheetpage.com/index.php/tip/pausing_a_macro_to_get_a_user_selected_range/
    
        Prompt = "Select a cell for the output."
        Title = "Select a cell"
    
        '   Display the Input Box
        On Error Resume Next
        Set GetUserRange = Application.InputBox( _
            Prompt:=Prompt, _
            Title:=Title, _
            Default:=ActiveCell.Address, _
            Type:=8) 'Range selection
    
    '   Was the Input Box canceled?
        If GetUserRange Is Nothing Then MsgBox “Canceled!”
    End Function
    
  • 4

    这有点令人费解,但获取文本>清除剪贴板>放回文本:

    [A32:Q32].Copy
    With New DataObject
        .GetFromClipboard
        s = .GetText
        .Clear
        .SetText s
        .PutInClipboard
    End With
    

    当范围中的单个单元格文本不同时, Range.Text 返回 Null .

相关问题