Spreading a list to multiple worksheets

[Pulte]

Hi All -

I have a list of regions on one worksheet, say cells A10:A50, and I have 40 other worksheets in the same workbook. Starting with the first value in the list, I want to copy it to cell A1 on the first worksheet, and so on so that each of the other worksheets has one unique value in cell A1.

Can someone please help me with code for this?

[JBeaucaire]

Assuming all 40 sheets really do exist and you don't need sheets added for you (which might actually be easier)...

Please Login or Register  to view this content.

This macro will spot the number of actual values in the range, then copy them into A1 starting on the second sheet.

[excelxx]

Hi

I think you will need something like the code below

Please Login or Register  to view this content.

Please amend according to your requirement. The code current assumes that the worksheets already exist and that it is just 40 rows from A10 that are being evaluated.

Hope it helps

[Pulte]

Great. That worked. Thanks for the help.

[JBeaucaire]

ExcelXX, you are writing your code from a human perspective, activating cells and offsetting...in VBA you do not have to address cells in the same manner. The macro recorder shows you code like that because it is recording human actions, not good methods.

Physically activating or selecting cells should be unnecessary 99% of the time, so look for ways to process your data without it.

BTW, what did "assuming row 1 has headers" mean in your code? Was that an errant comment?

=========

Pulte, if that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[excelxx]

Hi JBeaucaire

Thank you for the feedback. I'll bear it in mind when writing code in the future.

Yes the comment about row 1 was an errant comment, when I initially wrote the code it started evaluating from the A1. As a result, I started the copying process from the second row.