Array of worksheet names

[ldarley]

Hi

Not sure what is going on here obviously I am doing something wrong!

I have an array of worksheet names, it is very simple:

Dim nameArr As Variant
        nameArr = Array("Pending", "Dossier", "Sheet3", "Sheet5", "Sheet4", "Sheet2")

I was hoping to cycle through the sheets to insert a picture using a For Next Loop however I am getting a runtime error 424 when I try to use nameArr(i) to identify the sheet to add the picture to (i has been declared as an integer).

Same error if I just do a simple nameArr(0).Select

How should I be taking the worksheet name from the array and dropping it back into the code?

Many Thanks

 

[ldarley]

Yes they are the code name for the sheets (as seen in the properties window fist line). All the sheets are in the active workbook, I have my code in module1

Using just nameArr(0).Select gives me error 424

If I push the name as shown in the worktabs into the array it does work with the Sheets(nameArr(i)) line you gave me but I was hoping to use the code name just in case someone renamed my worksheets

 

[Fluff]

Is the code in the same workbook as the sheets you are trying to access?

 

[ldarley]

Yes

 

[Fluff]

In that case something lie this should work

   Dim nameArr As Variant
   Dim i As Long
   
   nameArr = Array(Pending, Dossier, Sheet3, Sheet5, Sheet4, Sheet2)
   For i = 0 To UBound(nameArr)
      Debug.Print nameArr(i).Name
   Next i

If you get an error 424 Object required, then that particular sheet does not exist in the workbook with the code.

 

[ldarley]

Brilliant thanks Fluff, I knew it was something simple I can't believe it was just the quotation marks around my array of names.

So appreciative of your help!

 

[Fluff]

You're welcome & thanks for the feedback.