Copy a row record to a sheet based on the date entered in to a cell

Hi All,
I have scoured lots of forums and still cannot get the code for this to work.
I have a workbook with 13 sheets, sheet 1 is "Main Data" the other twelve are the months of the year Jan to Dec
On the Main Data sheet contains thousands of rows/records there are 3 columns where the users can enter dates, column C "Inception" column D "First response" and column E "Future Meeting".

What I need is that if any user enters a date in to any of the 3 columns then that row/record is copied and the values pasted to the next empty row on the relative month sheet, the copy and paste should happen once the user presses the enter button on the keyboard after entering a date.

Looking forward to any answers

Gary

Hello,

To be clear, you are saying that a user entering a date into any of three cells on the main page and pressing [enter] will result in that date being pasted as a value into the sheet representing that month?

There is no extra data to input or paste over? Are the month sheets named, or numbered?

Hello, sorry this reply took so long... i'm still learning about excel code myself so it took me a while to work out all the bugs.

I believe this will work for you but i stress that you really should save and backup your work, and use a test sheet beforehand because you may ned to alter the code slightly!

you should paste this into the "microsoft excel objects" area for the main sheet:

Please Login or Register  to view this content.

As a note, the part of the code that says "Sheets(strMonth).Cells(23000, 1).End(xlUp).Row).Address" is telling excel to start at A23000 and press "end" then "up" to find the last row of your data. If your column A is not used, change the 1 in Cells() to a column which is always filled in. If your data list extends beyond row 23000, change the 23000 to something much larger.

Also, the rngToCopy is pointed to sheet1 in your workbook - you should change it to say "Sheets("Main Data")" or whatever your main data sheet is called

I hope this works, i am available to troubleshoot it if not

Oh, and since you didn't specify that you didn't want the information on the main data page to dissapear once it was copied and pasted, there isn't really a user feedback that it has happened.

If you'd like, you can either clear the cells by writing the following just before the bit in the code that says "else: exit sub":

Please Login or Register  to view this content.

or, you can give a user feedback (cleared by pressing the enter key), by pasting to the same place:

Please Login or Register  to view this content.

Im thrilled that it worked!
May i ask whether you opted for a user feedback?

Either way, im glad to help.

Hi Jay,

I moved the file to another machine that only uses 2007 and each time I try it this line of code comes up as stopping it from running.

Any ideas?

Gary


Set rngToPaste = Sheets(strMonth).Range(Rows(Sheets(strMonth).Cells(23000, 1).End(xlUp).Row).Address)