combining two VBA codes

[steve400243]

Hello, I need to have a way for both codes to work together in my worksheet. They both work separately. Please see below code and Thanks for all help provided. It is really great help.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if column C or E updated
    If Target.Column = 3 Or Target.Column = 5 Then
'       Update cell to right
        Target.Offset(0, 1) = Now()
    End If
    -------------------------------------------------------------------------------------

  If Not Intersect(Target, Range("A:A")) Is Nothing Then
     If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
     Dim Lastrow As Long
     Dim Lastrowa As Long
     Lastrow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
    

     If Target.Value = "shipped" Then
        Target.Resize(, 14).Copy
        If Target.Value = "shipped" Then Sheets("shipped").Range("A" & Lastrow).PasteSpecial (xlPasteValues)
                 Application.EnableEvents = False
          Rows(Target.Row).Delete
        Application.EnableEvents = True
     End If
  End If
End Sub

 

[steve400243]

I suggest that you co back and read post 4 again.
That will NOT work if column A contains formulas! As I said, Worksheet_Change event procedures do NOT work on changes made by formulas!
They only work on manually updated data.

Since the formula looks at columns D and F, then you would need to do it the same way we did the other one I helped you with yesterday, i.e.

If Target.Column = 4 or Target.Columns = 6 Then

Then, once inside that IF...THEN block, you can check the values in any columns of the Target row like this, for example:

'Column A
If (Cells(Target.Row, "D") = "") And (Cells(Target.Row, "F") = "") And (Cells(Target.Row, "A") = "Shipped") Then

Looks like I was able to get it working once I slowed down and read everything you provided. Cant thank you enough.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if column C or E updated
    If Target.Column = 3 Or Target.Column = 5 Then
'       Update cell to right
        Target.Offset(0, 1) = Now()
    End If
    

  If Not Intersect(Target, Range("A:A")) Is Nothing Then
     If (Cells(Target.Row, "D") = "") And (Cells(Target.Row, "F") = "") And (Cells(Target.Row, "A") = "Shipped") Then Exit Sub
     Dim Lastrow As Long
     Dim Lastrowa As Long
     Lastrow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
    

     If Target.Value = "shipped" Then
        Target.Resize(, 14).Copy
        If Target.Value = "shipped" Then Sheets("shipped").Range("A" & Lastrow).PasteSpecial (xlPasteValues)
                 Application.EnableEvents = False
          Rows(Target.Row).Delete
        Application.EnableEvents = True
     End If
  End If
End Sub

 

[Joe4]

Is that really working the way you need it to in all cases?

So, the first section updates columns D and F based on changes to columns C and E.
And the formula in column A depends on the values in columns D and F, right?
And you want something to happen when column A changes, right?

I think there may be a cleaner to put his altogether, but I would need a better understanding how all this relates to each other, and what essentially is supposed to happen?

 

[steve400243]

Is that really working the way you need it to in all cases?

So, the first section updates columns D and F based on changes to columns C and E.
And the formula in column A depends on the values in columns D and F, right?
And you want something to happen when column A changes, right?

I think there may be a cleaner to put his altogether, but I would need a better understanding how all this relates to each other, and what essentially is supposed to happen?

Wonderful, I am open to your suggestions.

This is sheet is used to track inbound shipments starting on Row 3
a user updates cell C3 with a inbound Number, for example 123. then the current date will update in cell D3 as the received date
Then the user will update cell E3 with a quantity out, 50 for example. the current date will then populate in cell F3 as the shipped date.
Once shipped the whole row moves to the Shipped Tab.

Thank you Joe4 -

 

[Joe4]

I think this should cover it all. Note that I added some variables just to make some of the coding a little shorter, since we refer to the target row and column so much in the code.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim c As Long
    Dim LastRow As Long

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Get current row and column of Target that was just updated
    c = Target.Column
    r = Target.Row
    
'   Check to see if column C or E updated
    If c = 3 Or c = 5 Then
        Application.EnableEvents = False
'       Update cell to right with date
        Target.Offset(0, 1) = Now()
'       Check to see if column A is "Shipped"
        If Cells(r, "A") = "Shipped" Then
'           Find last row on shipped tab
            LastRow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy and paste row to "Shipped" tab
            Range(Cells(r, 1), Cells(r, 15)).Copy
            Sheets("shipped").Range("A" & LastRow).PasteSpecial (xlPasteValues)
            Application.CutCopyMode = False
'           Delete original row
            Rows(r).Delete
        End If
        Application.EnableEvents = True
    End If

End Sub

 

[steve400243]

I think this should cover it all. Note that I added some variables just to make some of the coding a little shorter, since we refer to the target row and column so much in the code.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim c As Long
    Dim LastRow As Long

'   Only run when a single cell is updated
    If Target.CountLarge > 1 Then Exit Sub
   
'   Get current row and column of Target that was just updated
    c = Target.Column
    r = Target.Row
   
'   Check to see if column C or E updated
    If c = 3 Or c = 5 Then
        Application.EnableEvents = False
'       Update cell to right with date
        Target.Offset(0, 1) = Now()
'       Check to see if column A is "Shipped"
        If Cells(r, "A") = "Shipped" Then
'           Find last row on shipped tab
            LastRow = Sheets("shipped").Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy and paste row to "Shipped" tab
            Range(Cells(r, 1), Cells(r, 15)).Copy
            Sheets("shipped").Range("A" & LastRow).PasteSpecial (xlPasteValues)
            Application.CutCopyMode = False
'           Delete original row
            Rows(r).Delete
        End If
        Application.EnableEvents = True
    End If

End Sub

Works great, thank you Joe4, I really appreciate all the tips, and this improved code. Have a great day.

 

[Joe4]

You are welcome!