VBA code to copy ActiveSheet.name to the Clipboard?

[actjfc]

Excel friends, this should be an easy one: What is the code to copy the ActiveSheet.name to the clipboard? So, I can paste it anywhere I want outside the Excel workbook. Any help is appreciated. Thanks!

 

[MickG]

Try this:-
You need to double Click cell "A1" to load the "ClipBoard"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyData As New DataObject
If Target.Address(0, 0) = "A1" Then
    Set MyData = New DataObject
    MyData.SetText ActiveSheet.Name
    MyData.PutInClipboard
End If
End Sub

 

[actjfc]

I could not make it work, where do I to copy this code? I would like to copy it as a regular module in Personal.xls with a short cut. So, It should work in any workbook. I want to pass it onto other person, so I do not want to go on explain setting up library references, is needed. The double click in A1 makes it "cumbersome" if compared to a single shortcut. Thanks!

 

[pgc01]

Hi

Similar to Mick's.

Please try:

Sub Test()
Dim oDataObj As Object

Set oDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
oDataObj.SetText ActiveSheet.Name
oDataObj.PutInClipboard
End Sub

 

[MickG]

On "Developer tab"
Click "Record Macro"
Nominate a letter with "Ctrl"
Select "Personal Workbook Macro"
Write description if reguired.
Do something on the sheet.
Click "Stop Recording"
Click "Macro"
Select Recorded Macro from Dialog Box.
Click "Edit Macro"
Vb window appears
Paste (Red) code as below in Code window
Close Vb window.
Test Macro by Clicking "Ctrl +p" (Whatever)
Select cell, select Paste:- Sheet name appears in cell.
Try On External file ("Whatever")

Sub Macro9()
'
' Macro9 Macro
'
' Keyboard Shortcut: Ctrl+p
Dim MyData As New DataObject
Set MyData = New DataObject
MyData.SetText ActiveSheet.Name
MyData.PutInClipboard
End Sub

 

[RoryA]

Mick,
You need a reference to the MSForms library for your version to work as it's early bound.

 

[actjfc]

Thanks to all! pgc01 macro works perfect. Thanks! again!

Only one question: what do you do with New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69} can it be any other string with the same lenght? Just curiosity. Thanks!

 

[MickG]

Rory
When I click on that Ctrl (to untick it) in the Reference library it says control in use, does this mean it is selected by default because the actual opening of the File requires it.
If not what the alternative to this or PGC's

 

[RoryA]

No, it's not selected by default - unless you have added a userform to the project. To use early binding you have to have the reference; the code PGC provided is the only way to late bind it (you can use GetObject as well as CreateObject, it makes no difference).

 

[RoryA]

what do you do with New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69} can it be any other string with the same lenght?

No - that's the CLSID for the MSForms.DataObject. You can use other CLSIDs to create objects but it isn't usually necessary - the ProgID is usually easier to remember (like "Excel.Application") - it's just that the ProgID doesn't work for MSForms objects for some reason.