Copy Data Range Between Blank Cells in Column

So I've got a seemingly simple problem that I haven't been able to figure out.

In column A, I have dates. Column B has a job type. Column C has payment. Here's a sample workbook to show what I mean
Sample.xlsm

Now, what I want to do has two parts:
First I want to find the first instance of Job "1" (B1), then count down until it reaches Job "2" (B6).
The second part is to copy all the cells from A1 to C5 (All of the Job 1s on 12/1). Then paste that data into the sheet "Job1".
Then continue and find the next instance of job "1" in column B and count down until it reaches the next job type, etc.

Now, I can figure out how to get the second part to work. I'm having trouble with the first - counting the cells between Job "1" and "2" in order to copy only the data pertaining to job "1"

I realize this would be easy if I just filled B2:B5 with job "1", but my boss likes the current layout.

Thanks

Originally Posted by tigeravatar

Does your actual workbook have headers in row 1 (or elsewhere, as opposed to no headers at all)?
Does your actual workbook already have sheets named 'Job1', 'Job2', etc or do those need to be created?
Is the worksheet naming scheme 'Job1', 'Job2', etc accurate?

Yes, there are headers. Let's say they're Date (A1), Job (B1), and Pay (C1).

The sheets already exist, and no the naming scheme isn't accurate (there's actually a lot more information involved, but I'm simplifying things).

Really what I need is that first part - counting the blank cells between Job 1 and 2 so that I can copy and paste the range (which I believe I can manage myself).

Thanks

Originally Posted by tigeravatar

rs235251,

Alright, this should get you started then:

Thanks, that works brilliantly in that example.

I'm having a little trouble getting it to work with my workbook though. I didn't expect this to be a problem, but the cells in column B aren't actually called 1,2,3. They have text-based names. The sheets they're being copied to are named the same, still. I've tried tweaking the

Please Login or Register  to view this content.

part of the macro you wrote and I'm getting a bunch of different errors with everything I attempt.

Sorry for the confusion. What you wrote is largely out of my VBA skill level.

There is no naming convention. It's just xxx, yyy, zzz. All unrelated in name.

Really appreciate the help

Originally Posted by tigeravatar

If that's the case, give this a try:

Please Login or Register  to view this content.

That's great, working really well.

Only one problem though - when it copies over the most recent job in the master log, it copies everything blank below it. So it's copying down to row 1048576.

You may have a better idea, but I think an if/then statement might work where if it found more than, say, 30 blanks after the last job then it would stop?

That's amazing. You're fantastic

Ok, new day and a followup question...

If I wanted to copy all the entries for jobs 2 & 3 into Sheet5, but not job 1, how would I do that?

I was trying out various tweaks reusing this bit of code from above:

Please Login or Register  to view this content.

...using an if/then statement to delete job 1 as a criteria in arrUnq. It worked - but when it copied over to Sheet5 it was sorted by job and I wanted it to be sorted by date and then job. Didn't manage to get that part to work, and subsequent attempted tweaks have broken the copying over at all..

Thanks

Pl see the code below. It may help .

Please Login or Register  to view this content.