combining two VBA codes

steve400243

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
VBA Code:
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:
VBA Code:
'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.

VBA Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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.
VBA 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
 
Solution

steve400243

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
VBA 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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
:)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,205
Members
425,267
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top