0

I am new to excel macros. I want to copy the selected range to clip board.

Below is the link where half solution is present, it gives output in popup msg box.

Excel VBA get range of user selected range by mouse

its like

Sub macro1()
  MsgBox Selection.Address(ReferenceStyle:=xlA1, _
                           RowAbsolute:=False, ColumnAbsolute:=False)
End Sub

But if i want to escape the popup and directly copies the result to clipboad????

example: if i have selected the cells from B15 to E40 and F23 cell, it gives the msg as "B15:E40,F23", and i want to copy this msg, not the cell content of these selected cells.

Community
  • 1
  • 1
namit
  • 6,780
  • 4
  • 35
  • 41

1 Answers1

3

A simple Selection.Copy will place the selected range to clipboard :)

FOLLOWUP

To copy the Cell Address of the selected range do this.

'~~> Set a reference to Microsoft Forms Object Library
Sub Sample()
    Dim strAddr As String
    Dim MyDataObj As New DataObject

    strAddr = Selection.Address

    '~~> This will put the address string in the Clipboard. To test this
    '~~> After you run this macro, press CTL - V in Notepad.      
    MyDataObj.SetText strAddr        
    MyDataObj.PutInClipboard
End Sub

MORE FOLLOWUP

i want to replace commas with forward slash????

As mentioned in my comment, replace the comma with the Slash.

strAddr = Selection.Address
strAddr = Replace(strAddr, ",", "/")
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Wrong!! It copies the cell content, not the output range! example: if i have selected the cells from B15 to E40 and F23 cell, it gives the msg as "B15:E40,F23", and i want to copy this msg, not the cell content of these selected cells. – namit Jun 24 '12 at 12:50
  • 3
    `Wrong!! It copies the cell content, not the output range!` Relax :) There is no need to jump your guns ;) I have updated the above post... – Siddharth Rout Jun 24 '12 at 13:19
  • its gives an error msg as: "Compile error: User-defined type not defined" – namit Jun 24 '12 at 13:22
  • It feels like.. you are very near to the solution.. . :) – namit Jun 24 '12 at 13:26
  • 1
    Please read the first comment in the code ;) `'~~> Set a reference to Microsoft Forms Object Library` – Siddharth Rout Jun 24 '12 at 13:48
  • i am using ms office 2003 sp3 ... that is missing in reference or i am not able to locate it. how to fix it??? – namit Jun 24 '12 at 14:17
  • In VBA Editor, Click on `Tools | References` and then in VBA References Dialog box, Click on Browse and then select the FM20.Dll from the C:\Windows\system32 folder. Alternatively you can add a userform to your vba project which will automatically set a reference to it :) Once done, then run the above code again. – Siddharth Rout Jun 24 '12 at 14:36
  • So were you able to make it run? – Siddharth Rout Jun 25 '12 at 05:21
  • :) Its working I have modify it little bit, i have modified "Selection.Address" to "Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)" thanks.. . – namit Jun 25 '12 at 05:43
  • just as a curiosity;example i am getting output as "G17:J17,H18:J19,J20:J22,J24:J28,I25,G26:G28,E20:E25,F25,F18,C23:D23 " but if i want to get it like... "G17:J17/H18:J19/J20:J22/J24:J28/I25/G26:G28/E20:E25/F25/F18/C23:D23" " , i.e. i want to replace commas with forward slash???? – namit Jun 25 '12 at 06:03
  • Use the Replace function before copying it to the clipboard. See the updated post. :) – Siddharth Rout Jun 25 '12 at 06:05
  • Done...Done...Done.. Thanks... :) – namit Jun 25 '12 at 06:11