How to use array in worksheets()

[PumSese]

Hello I'd like to copy from one worksheet to multiple but I don't know if it is possible to use array in worksheets().

Please Login or Register  to view this content.

[Alf]

As you have defined your array and its elements in the macro it's "read in" when you run your macro.

As the array contains 18 elements a code for looping through sheets in a workbook and pasting the array values to each sheet could look like this:

Please Login or Register  to view this content.

Alf

[ikboy]

Try this:

Please Login or Register  to view this content.

[mohadin]

well I think this what you want

Please Login or Register  to view this content.

And in general to copy set of ranges

Please Login or Register  to view this content.

[sintek]

As all others are the norm...Here's Old school

Please Login or Register  to view this content.

[Rick Rothstein]

Another way to consider (assuming cell A1 on sheet "PoMil" contains the value to copy across)...

Please Login or Register  to view this content.

[mohadin]

Another way to consider (assuming cell A1 on sheet "PoMil" contains the value to copy across)...

Please Login or Register  to view this content.

It is not working on Excel2013!!!

[sintek]

@ Rick
Actually forgot about that option...

@ mohadin
Does work...Remember

assuming cell A1 on sheet "PoMil" contains the value to copy across

[mohadin]

FillAcrossSheets method of sheets class failed

[Rick Rothstein]

It is not working on Excel2013!!!

My latest version of Excel is 2010 and it works fine there. It is hard to believe that Microsoft removed this functionality, but I guess it is possible. Just out of curiosity, describe what does happen for you (and show any errors that you get)?

[mohadin]

The Error in #9 Sir

[Rick Rothstein]

The Error in #9 Sir

Error #9 is subscript out of range. The only thing I can think of for the code I posted is that one or more of the sheet names listed in the Array function call in my code does not match any sheet names in your workbook. Did you remember to change the OP's worksheet names (which is what I listed in the Array function call) to your own worksheet names?

[mohadin]

This what I'm doing

[Rick Rothstein]

This what I'm doing

You must include the sheet you activated as it is from that sheet that the copies will be made. So, just change this line of code in your macro...

Please Login or Register  to view this content.

to this...

Please Login or Register  to view this content.

and it should then work fine for you.

[mohadin]

Opps

So it's my fault
Thank you so much in Deed

[PumSese]

In the end i used.

Please Login or Register  to view this content.

Thank you all for help.