VBA Create Array For New Worksheets

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to search column B on the "All Data" sheet, and where a unique value is found create a new sheet and use the value from column B as the sheet name.

Please Login or Register  to view this content.

The question I have is could someone perhaps tell me please how I can assign a variable to create an array when the new sheets are created.

I'm assuming that the change is needed here, Worksheets.Add(After:=Sheets(Sheets.Count)).Name = rng.Value but I may very well be wrong.

Many thanks and kind regards

Chris

[patel45]

attach please a sample file for testing

[hobbiton73]

Hi @patel45, thank you for taking the time to reply to my post.

Unfortunately the file is large in size, so I was trying, if possible not to this, if that's feasible. I'm just really looking for some guidance which I can then take forward?

Many thanks and kind regards

Chris

[AB33]

Do you want this one?

Please Login or Register  to view this content.

[AB33]

Another one using your favourite topic

Please Login or Register  to view this content.

[hobbiton73]

Hi @AB33, thank you very much for coming back to me with this.

Forgive me, but could you tell me please where you create the array. I see the Array set as the variable but not in the rest of the script?

Perhaps it's my lack of knowledge.

Many thanks and kind regards

Chris

[AB33]

Chris,
The code could lead to lots of errors and wrong outcome. There was not need to create sheet arrays, hence that variable was not used on the code. If you look at my second code , I have amended few lines on the code. If the aim of the code was to create unique names, my second code works well.

[jolivanes]

Or with advanced filter

Please Login or Register  to view this content.

[jolivanes]

Or if you insist on an Array
This assumes a header in B1

Please Login or Register  to view this content.

[hobbiton73]

Hi @AB33, thank you very much for coming back to me with this with the explanation and amended code which works great.

All the best and kind regards

Chris

[hobbiton73]

Hi @jolivanes, thank you very much for taking the time to reply to my post and for the solutions you've put together as an alternative method to that shown by @AB33.

All the best and kind regards

Chris