If cell = X then copy entire row to new worksheet named X

Hey guys. I'm brand new to the forum and brand new to VBA. I had never used a macro until 4 days ago. So I've made some great progress using other individual's code to suit my needs to format a report I generate and create tables based on the data. However I'm stuck on a problem right now and my research hasn't turned up a solution.

Basically I need excel to search Column B, (Aisles) on my worksheet and create a new worksheet for every aisle. So if column B equals "01", then create a new worksheet titled 01, (or Aisle 01 if possible), and copy all aisle 01 rows to it. It would then create worksheets for aisles 02, 03, etc. There are 64 aisles, and 21,000 rows on this sheet. I've attached an example of the worksheet. I would appreciate any help on this. And detailed explanations are always welcome because I am trying to learn VBA for future application to different reports. Thank you in advance.

Thanks JB! I tried it on my example and it worked great, exactly as needed. However when running it on the full report I get a popup window that says:

"Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."

I have an i7 3770 processor and 12GBs of ram, so I can't imagine it's a problem with my computer. Any advice?

Well everything was working great on the test sheet, now the macro gets caught up at these lines:

ws.Range("A" & TitleRow + 1 & ":A" & LR).EntireRow.Copy _
Sheets(MyArr(Itm) & "").Range("A" & Rows.Count).End(xlUp).Offset(1)

Not sure what happened.

Ok, I officially feel like an idiot. When I retried the code in my test doc I used the wrong code (the one to copy data to existing sheets). Sorry for any confusion.

However my first problem, the one where excel is running out of resources, still exists. I'll try and stop adding posts unless I get asked a question.

Daniel,

Here's a question.
How does this one work for you?

Please Login or Register  to view this content.

Originally Posted by JBeaucaire

@kalak: eek! Looping through 20k+ rows and copying one row at a time... (shudder)

Jerry,

perhaps you might like to read the code, or even try it out before making that sort of negative and totally incorrect comment.

You're actually quite wrong. It doesn't copy "one row at a time". It copies all relevant rows in one lot.

And incidentally, it'll run significantly faster on any significant problem on these lines than anything you're likely to put up.

If you claim otherwise, then do a timing test on some data that can be replicated, post your results, and prove me wrong if you're able.

I try to help people on this forum, and having that sort of stuff poked at my codes without reason except (presumably) sloppy reading on your part helps nobody and does you personally no credit.