Results 1 to 8 of 8

Thread: Action list - Transfer to - Completed List. Paste Special (Values)

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    40
    Articles
    0
    Excel Version
    2016

    Action list - Transfer to - Completed List. Paste Special (Values)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Wondering if anyone can help.

    I have an Action list which when the action is complete (Column K = YES) it copies the line of data into the Completed worksheet.

    Now the only issue is I have formulas in some columns where I need the Values pasting across and not the formula.

    Im no expert in these matters!! And found this VBA elsewhere and it work well apart from this bit...

    Many Thanks in advance!



    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    'If Cell that is edited is in column U and the value is completed then
    If Target.Column = 11 And Target.Value = "YES" Then
    'Define last row on completed worksheet to know where to place the row of data
    LrowCompleted = Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Row
    'Copy and paste data
    Range("B" & Target.Row & ":N" & Target.Row).Copy Sheets("Completed").Range("B" & LrowCompleted + 1)
    'Delete Row from Project List
    Range("B" & Target.Row & ":N" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
    End Sub

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please wrap your code with code tags ( the #button). Thanks
    Thank you Ken for this secure forum.

  3. #3
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    40
    Articles
    0
    Excel Version
    2016
    Hi, unsure what you mean?

  4. #4
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    40
    Articles
    0
    Excel Version
    2016
    Code:
     Private Sub Worksheet_Change(ByVal Target As Range)
        On Error Resume Next
        Application.EnableEvents = False
        'If Cell that is edited is in column U and the value is completed then
        If Target.Column = 11 And Target.Value = "YES" Then
            'Define last row on completed worksheet to know where to place the row of data
            LrowCompleted = Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Row
            'Copy and paste data
            Range("B" & Target.Row & ":N" & Target.Row).Copy Sheets("Completed").Range("B" & LrowCompleted + 1)
            'Delete Row from Project List
            Range("B" & Target.Row & ":N" & Target.Row).Delete xlShiftUp
        End If
        Application.EnableEvents = True
    End Sub

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    40
    Articles
    0
    Excel Version
    2016
    Is that right??

  6. #6
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    86
    Articles
    0
    Excel Version
    O365
    How about
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error Resume Next
        Application.EnableEvents = False
        'If Cell that is edited is in column U and the value is completed then
        If Target.Column = 11 And Target.Value = "YES" Then
            'Define last row on completed worksheet to know where to place the row of data
            LrowCompleted = Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Row
            'Copy and paste data
            Sheets("Completed").Range("B" & LrowCompleted + 1).Resize(, 13).Value = Range("B" & Target.Row & ":N" & Target.Row).Value
            'Delete Row from Project List
            Range("B" & Target.Row & ":N" & Target.Row).Delete xlShiftUp
        End If
        Application.EnableEvents = True
    End Sub

  7. #7
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    40
    Articles
    0
    Excel Version
    2016
    Perfect!! Thank you 🙏

  8. #8
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    86
    Articles
    0
    Excel Version
    O365
    You're welcome & thanks for the feedback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •