Copying text boxes

SGH

Board Regular
Joined
Dec 11, 2003
Messages
72
I've written the following to copy Text Box 1 from one tab to another within a workbook. This works fine but when I want to delete the Text Box 1 its no longer that name, anyone know how I can keep the name as Text Box 1 after copying it ?

Sub PBTEN()
Sheets("PB").Select
ActiveSheet.Shapes("Text Box 1").Select
Selection.Copy
Sheets("Period Update").Select
Range("I28").Select
ActiveSheet.Paste
Range("A1").Select
End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Works fine for me if the textbox name is correct to start with:


ActiveSheet.Shapes("Text Box 1").Select

SHOULD BE

ActiveSheet.Shapes("TextBox1").Select
 

SGH

Board Regular
Joined
Dec 11, 2003
Messages
72
ActiveSheet.Shapes("Text Box 1").Select

SHOULD BE

ActiveSheet.Shapes("TextBox1").Select

Text Box 1 with spaces is the name that Excel gives the text box & if you take out the spaces the macro doesn't work full stop.

I have found a way round by creating a blank text box & using this.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,794
Messages
5,544,322
Members
410,603
Latest member
rseckler
Top