troubleshooting Question

VB Code to test if Shape contains text.

Avatar of Trygve Thayer
Trygve ThayerFlag for United States of America asked on
VB ScriptMicrosoft ExcelMicrosoft Office
4 Comments1 Solution23 ViewsLast Modified:
I have a macro that copies shapes from one sheet to another sheet.  What I would like to do is test if the shape has text.  If so copy it.  If not do not.  Below is the code.  If there is a cleaner way to do this I am all ears.

Sub Stop_1_53()

    ActiveSheet.Shapes("TextBox 111").Copy
    Application.Goto Sheets("53").Range("FI28")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 112").Copy
    Application.Goto Sheets("53").Range("FI29")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 113").Copy
    Application.Goto Sheets("53").Range("FI30")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 114").Copy
    Application.Goto Sheets("53").Range("FI31")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 115").Copy
    Application.Goto Sheets("53").Range("FI32")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 116").Copy
    Application.Goto Sheets("53").Range("FI33")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 117").Copy
    Application.Goto Sheets("53").Range("FI34")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 118").Copy
    Application.Goto Sheets("53").Range("FI35")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 119").Copy
    Application.Goto Sheets("53").Range("FI36")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 120").Copy
    Application.Goto Sheets("53").Range("FI37")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 121").Copy
    Application.Goto Sheets("53").Range("FI38")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 122").Copy
    Application.Goto Sheets("53").Range("FI39")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 123").Copy
    Application.Goto Sheets("53").Range("FI40")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 124").Copy
    Application.Goto Sheets("53").Range("FI41")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 125").Copy
    Application.Goto Sheets("53").Range("FI42")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 126").Copy
    Application.Goto Sheets("53").Range("FI43")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 127").Copy
    Application.Goto Sheets("53").Range("FI44")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 128").Copy
    Application.Goto Sheets("53").Range("FI45")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 129").Copy
    Application.Goto Sheets("53").Range("FI46")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 130").Copy
    Application.Goto Sheets("53").Range("FI47")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 131").Copy
    Application.Goto Sheets("53").Range("FI48")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 132").Copy
    Application.Goto Sheets("53").Range("FI49")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 133").Copy
    Application.Goto Sheets("53").Range("FI50")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 134").Copy
    Application.Goto Sheets("53").Range("FI51")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")
    ActiveSheet.Shapes("TextBox 135").Copy
    Application.Goto Sheets("53").Range("FI52")
    ActiveSheet.Paste
    Application.Goto Sheets("Create Blocks").Range("A2")

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros