Grab a Label

[Anne Troy]

Can we code the attached file?

User places cursor in a row.
Clicks button.
The data is copied to the clipboard in an address format.

I keep seeing this question asked again and again and again...

[XL-Dennis]

Anne,

Where is it supposed to be pasted in?

Kind regards,
Dennis

[Anne Troy]

Well, I kind of wanted it just *to the clipboard*, but I guess that's kinda stupid, huh?



Word! (Is there anything else? hee hee)

[Anne Troy]

Oh. If necessary, we can just go ahead and paste it at the *then current* insertion point in Word.

[XL-Dennis]

Anne,

Something along this line will do what You want:
(I?ve added some checks as well as errorhandling)

'Add this line after You have connected the procedure to the button.
Option Private Module
Option Explicit
Sub GetAddy()
Dim rnData As Range, rnArea As Range, rnSelect As Range, rnCopy As Range
Dim stData As String
Dim vaData As Variant
Dim lnRow As Long, i As Long
On Error GoTo HandleErr
Application.ScreenUpdating = False
Set rnSelect = Selection
Set rnArea = Range("A1:E4")
Set rnCopy = Range("IV1:IV3")
rnCopy.ClearContents
If Not Application.Intersect(rnSelect, rnArea) Is Nothing Then
With rnSelect
If Not .Rows.Count > 1 Then
lnRow = .Row
Else
MsgBox "You should only select one row!", vbInformation
GoTo ExitHere
End If
End With
Else
MsgBox "You must select a cell in the coloured area!", vbInformation
GoTo ExitHere
End If
Set rnData = Range("A" & lnRow & ":" & "E" & lnRow)
vaData = Application.Transpose(rnData.Value)
For i = 1 To 2
rnCopy(i, 1).Value = vaData(i, 1)
Next i
For i = 3 To 5
If i = 5 Then
stData = stData & vaData(i, 1)
Else
stData = stData & vaData(i, 1) & ","
End If
Next
rnCopy(3, 1).Value = stData
'Put it into the clipboard.
rnCopy.Copy
MsgBox "You may switch to MS Word and paste it in!", vbInformation
ExitHere:
Application.ScreenUpdating = False
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Module1.GetAddy"
Resume ExitHere
End Sub

Kind regards,
Dennis

[Anne Troy]

Thanks, Dennis! Swamped with client tasks today. Will check it tonight or tomorrow.

THANKS AGAIN!!

[Zack Barresse]

... Will check it tonight or tomorrow.

Have you had a chance to check this yet Anne? (I don't want to rush you )

[Anne Troy]

Sorry. Damn clients. Who do they think they are?? Hee hee...
It's finally slowing down a little bit!!
I'll get it this weekend to be sure. I've got a few others too.