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

 

[Fluff]

You need to refer to it like

Sheets(nameArr(i)).Select

 

[ldarley]

Thanks Fluff I have modified this but now I get a runtime error 9 subscript out of range whichever nameArr () value I use?

 

[Fluff]

In that case make sure that the names in the array are exactyy the same as shown on the sheet tab, especially check for any leading/trailing spaces.

 

[ldarley]

Weird as it doesn't seem to be that e.g.

If I put in Pending.Select the code selects the Pending worksheet but if I put in

Sheets("Pending").Select I get the runtime error 9

 

[Fluff]

In that case you are using the codename, not the sheet name, so you need to remove the quotes from inside the array & then you can use nameArr(0).Select

 

[Trixterz]

Weird as it doesn't seem to be that e.g.

If I put in Pending.Select the code selects the Pending worksheet but if I put in

Sheets("Pending").Select I get the runtime error 9

That will work if you change your sheet property (name) to Pending in VBA and if so then the code below will also work without the parenthesis...

Sheets(Pending).Select

 

[Fluff]

if you change your sheet property (name)

If by this you mean the codename, then that code will not work. It would be Pending.Select as the OP posted.

 

[ldarley]

That's right if I take out the parenthesis I get a type mismatch error 13

 

[Fluff]

Are those values the actual sheet name as seen on the tab, or are they the sheet codename which is the part before the brackets when you look at the project window in the editor?

Also are those sheets in the same workbook as the code?