How to copy one Shape (object) to multiple sheets?

[feipezi]

Hi,
I tried to copy a square with a product logo in it from one sheet to multiple sheets but failed. Here is the code:

Sub CopyOneToMany()
'On Error Resume Next
Dim rngarray As Variant
rngarray = Array("RegionAssignedAcctView", _
"RAMSummary", _
"GraphDisplayTC", _
"Graph_ALZ+_RAA1", _
"Graph_ALZ_Drill", _
"Display_Detail", _
"PayerbyGeography", _
"GraphDisplayMKT", _
"Graph_ALZ+_Drill", _
"Graph_ALZ_RAA1", _
"Graph_RAASumALZ+", _
"Graph_RAASumALZ")
For i = LBound(rngarray) To UBound(rngarray)
Sheets("sheet1").Shapes(1).Copy Sheets(rngarray(i)).Paste
Next
On Error GoTo 0
End Sub

Thanks in advance and Happy Holidays!
John

 

[SkipVought]

Hi,

I ran your code sucessfully with one addition: I defined i, the array index.

Have you stepped thru your code and observed what statement errors, and what the error is?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[feipezi]

I have no idea how you made it. But still not working for me.

Here is the new code with the defined 'i'. But get the error of "Run-time error: 1004 select method of worksheet class failed". When debugging, the problem is with 'Sheets(rngarray(i)).Select'.

Sub whatever()
'On Error Resume Next
Dim rngarray As Variant
Dim i As Double
rngarray = Array("RegionAssignedAcctView", _
"RAMSummary", _
"GraphDisplayTC", _
"Graph_ALZ+_RAA1", _
"Graph_ALZ_Drill", _
"Display_Detail", _
"PayerbyGeography", _
"GraphDisplayMKT", _
"Graph_ALZ+_Drill", _
"Graph_ALZ_RAA1", _
"Graph_RAASumALZ+", _
"Graph_RAASumALZ")
For i = LBound(rngarray) To UBound(rngarray)
Sheets("sheet1").Shapes(1).Copy
Sheets(rngarray(i)).Select
Range("r2").Select
ActiveSheet.Paste
Next
On Error GoTo 0
End Sub

When do without Array, then fine. Like this:

Sub Mcr()
Sheets("sheet1").Shapes(1).Copy
Sheets("PayerbyGeography").Select
Range("r2").Select
ActiveSheet.Paste
End Sub

Thanks again.
John

 

[PHV]

Are ALL the sheets (including sheet1) in the a SINGLE workbook ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

This is NOT your original code!

    Sheets("sheet1").Shapes(1).Copy
    Sheets(rngarray(i)).Select
    Range("r2").Select
    ActiveSheet.Paste

"When debugging, the problem is with 'Sheets(rngarray(i)).Select'"
EXACTLY what SHEET NAME is referenced? Is it the FIRST one in the array? I'd guess that that ARRAY NAME is not the exact name of a sheet in your workbook.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[WinblowsME]

Sub whatever()
   Dim rngarray As Variant
   Dim i As Double

   rngarray = Array("RegionAssignedAcctView", "RAMSummary", "GraphDisplayTC", _
                    "Graph_ALZ+_RAA1", "Graph_ALZ_Drill", "Display_Detail", _
                    "PayerbyGeography", "GraphDisplayMKT", "Graph_ALZ+_Drill", _
                    "Graph_ALZ_RAA1", "Graph_RAASumALZ+", "Graph_RAASumALZ")

   For i = LBound(rngarray) To UBound(rngarray)
      Sheets("Sheet1").Shapes(1).Copy
      
      Sheets(rngarray(i)).Select
      [COLOR=red]Sheets(rngarray(i)).[/color]Range("r2").Select
      
      ActiveSheet.Paste
   Next
   
   On Error GoTo 0
End Sub

 

[feipezi]

Thank you all, folks. Sorry but I did not get back to you promptly.

PHV, yes, they are all in a single workbook.

Skip, of course it's not the original code since I re-defined DIM i AS DOUBLE (even Single is not going to work). The error message was gone but the Shape was not pasted. I guess the problem was with the first tab; then I deleted the first tab; run it; then first tab again that used to be the second tab before the deletion.

Thanks again for your time.

 

[SkipVought]

Also, define integral counters like i as either Integer or Long: not Single or Double, which are INEXACT variable types.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}