0

Objective: trigger two separate worksheet_change(ByVal Target as Range) macros to copy data and paste into different tabs when:

  1. column J range = "Closed" ; and
  2. column G range is edited in any way.

The VBA below appears to be working for (1). But (2) appears only to be triggering the macro when the column G cell's data is deleted rather than input.

Option Explicit

Private Sub worksheet_change(ByVal target As Range)

    'Declare variables
    Dim CompletionDate As String
    Dim MsgGP As String
    Dim TitleMsg As String
    Dim CompletionComments As String
    Dim MsgGP2 As String
    Dim TitleMsg2 As String
    Dim RevisedDate As String
    Dim RevisedComments As String
    Dim MsgGP3 As String
    Dim TitleMsg3 As String

    TitleMsg = "xx"                              'Define InputBox text strings
    MsgGP = "xx"
    TitleMsg2 = "Road to $$"
    MsgGP2 = "xx"
    TitleMsg3 = "Task Deferral"
    MsgGP3 = "Deferral due to:"

    If Not Application.Intersect(target, Range("J" & ActiveCell.Row)) Is Nothing And InStr(1, Range("J" & ActiveCell.Row), "Closed") > 0 Then

        'If column J has changed and equals closed


        CompletionDate = Application.InputBox(MsgGP, TitleMsg, FormatDateTime(Date, vbShortDate), Type:=1) 'Create Input box to enter completion date

        If CompletionDate = "False" Then Exit Sub

        CompletionComments = Application.InputBox(MsgGP2, TitleMsg2, Type:=0) 'Create Input box to enter completion comments

        If CompletionComments = "False" Then Exit Sub

        Sheets("Plan").Range("B" & ActiveCell.Row & ":H" & ActiveCell.Row).Copy 'Copy columns B to H

        Sheets("Closed").Select                  'Select other worksheet

        Sheets("Closed").Range("i" & Rows.Count).End(xlUp).Offset(1) = CompletionDate 'Enter completion date

        Sheets("Closed").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues 'Paste work task data

        Sheets("Closed").Range("j" & Rows.Count).End(xlUp).Offset(1) = CompletionComments 'Paste completion comments

        Sheets("Plan").Activate                  'Open Plan worksheet

        Sheets("Plan").Range("D" & ActiveCell.Row & ":AV" & ActiveCell.Row).ClearContents 'Clear Contents in selected row

        Sheets("Plan").Activate                  'Open Plan worksheet

    End If

    If Not Intersect(target, target.Worksheet.Range("G" & ActiveCell.Row)) Is Nothing Then

        RevisedComments = Application.InputBox(MsgGP3, TitleMsg3, Type:=0) 'Create Input box to enter completion comments
        If RevisedComments = "False" Then Exit Sub

        Sheets("Plan").Range("B" & ActiveCell.Row - 1 & ":H" & ActiveCell.Row - 1).Copy 'Copy columns B to H

        Sheets("Revised").Select                 'Select other worksheet

        Sheets("Revised").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues 'Paste work task data

        Sheets("Revised").Range("j" & Rows.Count).End(xlUp).Offset(1) = RevisedComments 'Paste completion comments

        Sheets("Plan").Activate                  'Open Plan worksheet
    End If

End Sub

I'm sure there are many VBA 'best practice' tips to reduce this code. I would be grateful if you could pass on these tips along with a potential solution!

0m3r
  • 12,286
  • 15
  • 35
  • 71
Matthew Ng
  • 1
  • 3
    Here is one best practice tip - avoid Select in the code - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Vityata Nov 01 '18 at 16:16
  • 2
    I think your conditionals are needlessly complex and are contributing to difficulty in reading them. How about `If target.Column = 10 and target.Value = "Closed"` for the first one and `If target.Column = 7` for the second one? – StoneGiant Nov 01 '18 at 16:24
  • Look at using `select case target.column:case 10........end select` – Nathan_Sav Nov 01 '18 at 16:34
  • Mind your use of Range("J" & ActiveCell.Row). This event appears to fire after the change has been made and you may have moved to a different cell. If you add Debug.Print ActiveCell.Row before the If statement and have the Immediate Window open is it returning the row you expect? – Dave Nov 01 '18 at 17:11
  • Hi All, Thanks for your assistance so far. I'll look to amend the formatting to VBA best practice. To Dave: The active row appears to be flowing through correctly when de-bugging. To Nathan_Sav: Apologies, I don't quite understand the solution (VBA newbie!) Would you be able to elaborate a little? – Matthew Ng Nov 02 '18 at 00:28

0 Answers0