Automatic copy cell to another sheet if populated

[Jauron]

Excel 2007. Workbook has multiple sheets, one for each month and another sheet labeled Action Items. Within each month is a column that action can be entered into, along with other information (action item is not a requirement, so can be blank and don't want blank copied). Would like the action items sheet to self populate without having to create a line for each possibility (like a simple ='December 2012'!L2 etc would require).

I don't want the entire row, just the cell Action Item in the individual months so that dates and persons assigned can be recorded.

Sorry if I broke any rules so far.

[jeffreybrown]

How about attaching a sample sheet with a before and after?

[Jauron]

Attached. If you look at Action Items sheet you can see I"m pulling (as an example) from December 2012 sheet, Action column. Hopefully this gives you an idea of what I am trying to do.

[jeffreybrown]

Right click on the December 2012 sheet and paste macro below on the right side of screen.

Now when you place an action item in column L that action item will be transferred to the next available row on the Action Items sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim destSht As Worksheet: Set destSht = Sheets("Action Items")
    Dim destLR As Long: destLR = destSht.Range("C" & Rows.Count).End(xlUp).Row + 1
    If Not Intersect(Target, Range("L:L")) Is Nothing Then
        If destLR = 8 Then
            Target.Copy Destination:=destSht.Range("C9")
        Else
            Target.Copy Destination:=destSht.Range("C" & destLR)
        End If
    End If
End Sub

[Jauron]

You ROCK!! Thank you very much this is perfect.

[jeffreybrown]

Well you are most welcome and thanks for the feedback.

Happy Holiday's