Search for values, copy matching rows to existing sheet in a specific place?

Hi all,

I have a workbook where I have information categorized in several sheets.
Lets say they're named Red, Blue, Green, Yellow.

In each sheet I have a row that has data identified by date.
For example, Red Sheet has this data:
5/12/16 X-wing
2/12/16 TIE Fighter
4/12/16 Y-wing
1/12/16 Star Destroyer
2/11/16 Mon Calamari Cruiser

Now, the first sheet in the workbook is named Calendar.
It is organized like this:

Jan Days | January
Week 1 | (blank row)
Week 2 | (blank row)
Week 3 | (blank row)
Week 4 | (blank row)

Feb Days | February
Week 1 | (blank row)
Week 2 | (blank row)
Week 3 | (blank row)
Week 4 | (blank row)

(and so forth) The Days/Weeks are actually rows/columns that make up a mini calendar w/the days/dates. I want to automatically copy from each sheet (Red, Blue, Green & Yellow) the data row with a date into the Calendar sheet under the appropriate month in date order.
For example:

Jan Days | January
Week 1 | 1/12/16 Star Destroyer
Week 2 | (blank row)
Week 3 | (blank row)
Week 4 | (blank row)

Feb Days | February
Week 1 | 2/11/16 Mon Calamari Cruiser
Week 2 | 2/12/16 TIE Fighter
Week 3 | (blank row)
Week 4 | (blank row)

I've played with VLOOKUP, INDEX, IF's, MONTH, and some VBA code. I'm not getting it to work, so wonder if one of you can possibly help me out?

Sample spreadsheet here via Dropbox
I'd be fine with a button on 'January' that I'd have to click to run the function.

Many thanks in advance!

What do you want to see if you have two or more events in the same week?

Pete

Hey Pete,

Can you take a look at the sample workbook? It'll be easier to understand. The event rows aren't mapped to the calendar rows - it's hard to show it via text. Hopefully this screenshot will help.

Test_Workbook_Copy_and_Paste.jpg

I had already downloaded your file, but your opening description implied that each blank row was for a particular week.

Follow-up question, in the light of your comments: you have 9 blank rows for each month - what would you like to happen if you have more than 9 events in any one month?

Pete

Pete,

If there are more than 9 events, I'd like the additional events inserted above the following month, maintaining a buffer of one empty row between the Jan events and the Feb header.

Thanks!

A formula can't insert a row for you - you would need VBA for that then, and I don't dabble with macros anymore, so perhaps someone else will chip in.

Pete

Thanks for the clarification re: needing VBA to insert rows. Appreciate your time!

Originally Posted by Pete_UK

A formula can't insert a row for you - you would need VBA for that then, and I don't dabble with macros anymore, so perhaps someone else will chip in.

Pete

Pete, should I request a mod to move this post over to the VBA forum then?

Thanks!