How to copy text from RichTextBox?

Copy a text box from one book to another

• I use2007 on Windows Vista. Back in September(?) you showed how to copy a range of cells from one work book to the active sheet in any another book: Workbooks("Personal.xls").Worksheets("N2S legends").Range("A2:A8").Copy Range("F9"). I would like to pick up a "text box 1" from the same book and copy it to any other book/sheet. The following is a recorded macro that works ; but it needs the name of the destination sheet to operate,and therefore isn't universal. Application.ScreenUpdating = False Application.CutCopyMode = False Windows("Personal.xls").Activate Sheets("N2S legends").Select ActiveSheet.Shapes.Range(Array("Text Box 1")).Select Selection.Copy Windows("Data1.xlsx").Activate ActiveSheet.Paste Application.ScreenUpdating = True. I searched the VBA help, tried various substituions for the range term in the first macro and carried out trials to discover how VBA makes selections. The English/Plain language version of the task seems simple - go to a book/sheet; select what I want ; paste a copy into the place I started from. If you can help, thank you very much. I have often read and used your answers to shorten simple repetative tasks.

• Answer:

  In the example you showed, Workbooks("Personal.xls").Worksheets("N2S legends").Range("A2:A8").Copy Range("F9"). the Range("F9") reference is understood by VBA to be the ActiveWorksheet. So it could also be written as Workbooks("Personal.xls").Worksheets("N2S legends").Range("A2:A8").Copy ActiveSheet.Range("F9") the difference here is you are copying a textbox and that gets pasted to the sheet rather than a cell. However, it is pasted at the location of the activecell. We can use that understanding to genralize the macro and avoid the selecting of the source sheet. In your recorded code: Application.ScreenUpdating = False Application.CutCopyMode = False Windows("Personal.xls").Activate Sheets("N2S legends").Select ActiveSheet.Shapes.Range(Array("Text Box 1")).Select Selection.Copy Windows("Data1.xlsx").Activate ActiveSheet.Paste Application.ScreenUpdating = True. the Sceenupdating suppresses the visual feedback of changing the selection. If we don't change the selection, it isn't really necessary Workbooks("Personal.xls").Worksheets("N2S legends").Shapes.Range(Array("Text Box 1")).copy Activesheet.Paste would be the equivalent command to what you have. to specify where you want the textbox, you would need to select that cell first. So assume you want the upper left corner of the textbox above cell J7 then Range("J7").Select Workbooks("Personal.xls").Worksheets("N2S legends").Shapes.Range(Array("Text Box 1")).copy Activesheet.Paste You can shorten the code a little bit by addressing shapes directly Range("J7").Select Workbooks("Personal.xls").Worksheets("N2S legends").Shapes("Text Box 1").copy Activesheet.Paste If there are any problems, then post back.