macros to copy text to seperate sheet

  • I posted on another site some time ago and did not get a response

    I have a calendar and when names are added A5:N58 would like them copied to another sheet in column A
    If Jim - Bill - Jill is any were in A5:N58 they would appear in sheet 2 A2= Jim >> A3 = Bill >> A4 = Jill then if Jim was added again at later date then A5 = Jim
    if George added then A6= George this file is an example of calendar

  • Re: macros to copy text to seperate sheet

    It would probably be something along the lines of a worksheet change event, in the calendar sheet. But, alas, your VBE is locked down. :(

  • Re: macros to copy text to seperate sheet

    Put this into the sheet code of the Calendar Sheet and see if it does what you want.

    I tried to upload it back to you, but I guess the code made it too big to attach.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
        If Target.Column < 15 And Target.Row > 3 And Target.Row < 59 Then
            LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
                If LR < 3 Then LR = 3
                    Sheets("Sheet1").Range("A" & LR).Value = Target.Value
        End If
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!