add additional IF statement then split sheets further...

[yeders]

Hi guys,
Having a bit of trouble working out where it put it.


So the following, takes a block of Cells, and separates them based on unique values in column 9.
It then creates new tabs with each unqiue value in 9, and then it creates new worksheets also containing those filtered cells, based on column 9.
An example piece of data, has 3000 rows, and 8 unique values in column 9.

I want to add: If counta of a filtered value is >300 then divide by 2 and split those between two sheets; so say the unique value was "Hotel", then you would have "Hotel Sheet 1" & "Hotel Sheet 2".
If >600, then divide by 3 and split those between three sheets. >900, split between 4 sheets. (that should be enough).

Any thoughts? Appreciate the help!!!!!

Please Login or Register  to view this content.

[Bernie Deitrick]

Try this macro:

Please Login or Register  to view this content.

[yeders]

Hi there, thanks heaps for your help.

I have modified it further - to try and grab anything that is going to be split, to actually halve/third/quater it.

But I am getting an error here:

shtB.Range("A2").Resize((SplR - 1) * (i - 1)).EntireRow.Delete

So then I added the round functions into the SplR section, but then that didn't quite work, so then I defined it up front as '150' but then it gave me sheets in 150 chunks - suggesting that my attempt to split into equal parts, simply doesn't work.

If you could help me out with that logic, that would be great. I basically replaced "lngMRows" with "SplR", but the syntax on the calculations might be wrong.

Thanks!

Please Login or Register  to view this content.

[Bernie Deitrick]

If the sheet needs to be split, this will split it evenly:

Please Login or Register  to view this content.

[yeders]

Hey Bernie,

Thank you for that.

I am getting a "1004 error" Method "Name" of obejct '_worksheet' failed.
Referring to: shtT.Name = c.Value

The code I used is exactly the same as your laste, aside from: (lCol = 27)
I had to use =trim(left(A7,7)) to get the uniquiness of the strings to what I wanted.

Any thoughts?

-=-=-

Thanks for the code!

I think it was because i didn't have a value in the header row of that column 27!
All good.

[Bernie Deitrick]

Does the "All good" mean that you figured out the problem, or do you still need help?

[yeders]

Hi Bernie,
Sorry - "all good" means that I sorted it out, no more help necessary (for now :D). I think it was because i was missing some column headers.

Thanks