Copy row number of active cell to clipboard (Excel VBA)

Question

I want to create a macro that takes the row number of the active cell and copies it to the clipboard.

When I execute the following code I get 'compile error: invalid qualifier'.

I have forgotten most of my VBA but surely something this simple should work? Thanks in advance for any help you can offer.

Sub macro3()
    Dim x As Integer
    x = ActiveCell.row
    x.Copy
End Sub

EDIT: Using the solution below, I changed it to the following (which works):

After first adding a reference to Microsoft Forms 2.0 Object Library under Tools > References in the VBE:

Sub macro3()
    Dim x As DataObject
    Set x = New DataObject
    x.settext ActiveCell.row
    x.putinclipboard
End Sub

An `Integer` (which btw use `Long` instead) doesn't have a `Copy` method. — BigBen, Dec 12 '19 at 12:50
Have a look [here](https://en.wikibooks.org/wiki/Visual_Basic_for_Applications/Clipboard_VBA). Btw, can I ask you why you would want this number in your clipboard? — JvdV, Dec 12 '19 at 13:05
Have a look at this solution: https://stackoverflow.com/questions/14738330/office-2013-excel-putinclipboard-is-different — Netloh, Dec 12 '19 at 13:16
I want to copy the number to the clipboard so that I can use it in a formula to import that row's data to a new table. — tb2944, Dec 12 '19 at 14:27

BigBen · Answer 1 · 2019-12-12T13:54:07.027

2

One option, using a DataObject - add a reference to Microsoft Forms 2.0 Object Library under Tools > References in the VBE.

Sub Test()
    Dim x As DataObject
    Set x = New DataObject

    x.SetText ActiveCell.Row 'Or CStr(ActiveCell.Row) to make the type conversion explicit
    x.PutInClipboard
End Sub

edited Dec 12 '19 at 13:54

answered Dec 12 '19 at 12:56

BigBen

46,229
7
24
40

This won't work on Windows 10 since PutInClipboard is no longer supported. – Netloh Dec 12 '19 at 13:08
@Netloh Works for me on Windows 10 Enterprise. – BigBen Dec 12 '19 at 13:09
Weird. I haven't been able to use that method since upgrading. Need to make use of a Windows API instead. Like described here: https://stackoverflow.com/questions/14738330/office-2013-excel-putinclipboard-is-different – Netloh Dec 12 '19 at 13:14
Works in Windows 10 Pro as well. But you either need to add a UserForm or browse for FM20.dll in order to add the `Microsoft Forms 2.0 Object Library`. – Michael Wycisk Dec 12 '19 at 13:20
Thank you, using the DataObject variable worked for me (on Windows 10). I tried using Long variable, without success. – tb2944 Dec 12 '19 at 13:49
Because `DataObject` works with text (`String`). Not sure why you need a `Long` here anyway. Maybe edit your question with the revised code you're trying? – BigBen Dec 12 '19 at 13:49
Done. Does that mean that the row number is text? – tb2944 Dec 12 '19 at 14:36
No. The row number is a long. It's just that `DataObject` works with text. [Here's](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dataobject-object) some more reading. – BigBen Dec 12 '19 at 14:37

Michael Wycisk · Answer 2 · 2019-12-12T14:00:44.167

2

You might have a look at this answer. Since late binding is used, you do not need to add a reference.

Your code would look something like (the private sub is copied from the answer that I linked to above):

Sub RowNumberToClipboard()
    Dim lRow As Long
    lRow = ActiveCell.Row

    Call CopyText (CStr(lRow))
End Sub


Private Sub CopyText(Text As String)
    'VBA Macro using late binding to copy text to clipboard.
    'By Justin Kay, 8/15/2014
    Dim MSForms_DataObject As Object
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    MSForms_DataObject.SetText Text
    MSForms_DataObject.PutInClipboard
    Set MSForms_DataObject = Nothing
End Sub

edited Dec 12 '19 at 14:00

answered Dec 12 '19 at 13:01

Michael Wycisk

1,590
10
24

Thanks @BigBen that makes sense. I tested the code and it works (even without converting the long to a string), but of course I agree that adding the `CStr()` is a better solution. – Michael Wycisk Dec 12 '19 at 13:08
You should get a `ByRef argument Type Mismatch` error without the `CStr`. I'm not sure how you got your original code to work without the `CStr`. – BigBen Dec 12 '19 at 13:09
@BigBen I don't want to argue about it since I definitely agree with you. But I tested the code without `CStr()` and it throws no error. – Michael Wycisk Dec 12 '19 at 13:13
Would also work if you dropped the `As String` (though that's a bad idea too). – BigBen Dec 12 '19 at 13:15

Copy row number of active cell to clipboard (Excel VBA)

2 Answers2