record a macro to copy data and paste into next empty column on another worksheet?

[nimv1]

Hi guys,

I hope you can help, because I am stuck in doing the following:

What I would like to do is create a macro which will copy data from cells D5:D9 (in sheet 1) and paste into cells D4:D8 in sheet2.
The following month, I would like the data to be copied from the same cells, but pasted into cells E4:E8 and so on.
Please see attached for example.
I have tried using option explicit, but was getting syntax error.

I hope you can help?

Regards

N

[AB33]

This macro will copy in to next column but the same row

Please Login or Register  to view this content.

[protonLeah]

Please Login or Register  to view this content.

[nimv1]

Thanks for the reply, but this does not work.
This copies the data, but pastes over the data again. I am looking for a macro to copy data into the next empty column?
Any ideas?

[protonLeah]

I don't understand. You said the next month copy to the next column. My macro determines the current month and copies it there as suggested by the sheet with month headers for each column?

[nimv1]

My apologies, I thought I would attach a simplified sample with doing this on a monthly basis.
It is done on a weekly basis and the day is not always the same.
Therefore I was hoping someone can help with the option explicit code and knows how to identify the next blank cell?

Regards