If all you want is to put this string onto your clipboard for pasting elsewhere, then:
In your workbook module:
VBA Code:
Private Sub Workbook_Open()
Run "MakeMenu"
End Sub
Private Sub Workbook_Activate()
Run "MakeMenu"
End Sub
Private Sub Workbook_Deactivate()
Run "RightClickReset"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightClickReset"
End Sub
In a standard module:
VBA Code:
Private Sub RightClickReset()
On Error Resume Next
CommandBars("Cell").Controls("CopyVisibles").Delete
Err.Clear
CommandBars("Cell").Reset
End Sub
Private Sub MakeMenu()
Run "RightClickReset"
Dim cb As CommandBar, MenuObject1 As CommandBarButton
Set cb = Application.CommandBars("Cell")
Set MenuObject1 = _
cb.Controls.Add(Type:=msoControlButton, before:=1)
With MenuObject1
.Caption = "CopyVisibles"
.OnAction = "KoppyViz"
End With
Application.CommandBars("Cell").Controls(2).BeginGroup = True
Set cb = Nothing
Set MenuObject1 = Nothing
End Sub
Sub KoppyViz()
Dim cell As Range, strVal As String
For Each cell In Selection.SpecialCells(12)
strVal = strVal & cell.Value & ","
Next cell
strVal = Chr(39) & Left(strVal, Len(strVal) - 1)
Dim DatObj As New DataObject
DatObj.SetText strVal
DatObj.PutInClipboard
End Sub
Note that I added the apostrophe character (ascii 39) because if you were to paste these numbers into a cell, Excel will think you are trying to enter a large number greater than 15 digits and hence you will paste a scientific notation that will be useless. Of course if the data will be text, you do not need the preceding apostrophe character.
After you install the code as I directed, save the workbook, and either...
...close the workbook and reopen it
or
...activate a different workbook in that same instance of Excel and then reactivate the workbook containing this code.
Then, right click any cell in that workbook and the first item on the right-click menu will be a button captioned "CopyVisibles" Just click that button after you selected whatever cells you want to build this comma-delimited string from, and the values in the visible cells of that selected range will be the string on your clipboardto be pasted later.
From there, hitting Ctrl+V would paste the string.