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:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMonth As String
    Dim rngToCopy As Range
    Dim rngToPaste As Range
    
    'this IF will activate only cells in Column C,D, or E when the cell value changes and enter is pressed
    If Target.Address = Range("C" & Target.Row).Address Or Target.Address = Range("D" & Target.Row).Address Or Target.Address = Range("E" & Target.Row).Address Then
    
    'this strMonth turns the value entered into a Month name eg "January"
     strMonth = Format(Target, "mmmm")
    
    
    'define the Rows of information to copy and paste
    Set rngToCopy = Sheets(1).Range(Rows(Target.Row).Address)
    Set rngToPaste = Sheets(strMonth).Range(Rows(Sheets(strMonth).Cells(23000, 1).End(xlUp).Row).Address)

        'use the Month name to determine the worksheet to use
        
            rngToCopy.EntireRow.Copy Destination:=rngToPaste.Offset(1)

    Else: Exit Sub
    
    End If

End Sub

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

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)

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":

rngtocopy.clearcontents

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

Msgbox("This entry was printed to the " & strMonth & " sheet")