I have 2 sheets, one with data ("Data") and another is the output sheet ("Calendar"). I'm trying to write a macro that will copy a row of data and paste it in the calendar sheet under a respective month (depending on the date in copied data), and after the data has been placed in the calender sheet. The macro should fill in (with color) the cell corresponding to the type of audit performed and day of the month

For example. Copy from Data sheet- A1, B1, C1, and D1 and paste in Calendar sheet- B20, C20, D20, and AP20, respectively.

Sample.xlsm

This process needs to be repeated until all the data has been placed on the calendar.

And there needs to be a mechanism to remove a certain location (with respective date, audit type, & comment). I'm thinking remove the line from the data sheet then have the calendar columns/rows cleared and completely repopulated.

I've already written some formulas to color in a certain day of the week corresponding to the type of audit performed (after it's been placed in the audit type column in the calendar sheet). Also, I've written code to populate the calendar based on the year, month, and start day.
After that I'm starting to draw a blank on the best method to do so.

Any suggestions!? Thanks in advance.