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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
If they both work inidividually, I do not see any reason why they wouldn't work when both are placed together (one under the other) in a single "Worksheet_Change" event procedure.

How is it not working?
Can you walk us through an actual example where it is not working?
 

steve400243

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If they both work inidividually, I do not see any reason why they wouldn't work when both are placed together (one under the other) in a single "Worksheet_Change" event procedure.

How is it not working?
Can you walk us through an actual example where it is not working?
Hello Joe4, thank you for looking at it. When cell A3, A4, etc changes to "Shipped" using a cell formula it should move the row to the Shipped Tab. I may be doing something wrong, but it is not working for me. I tried like this, but get a compile error.

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()
 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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
When cell A3, A4, etc changes to "Shipped" using a cell formula it should move the row to the Shipped Tab.
Worksheet_Change event procedures do NOT run when cells are changed via formula!
They only run when cells are manually updated (manual entry, copy/paste).

If cells A3 and A4 contain formulas, then you have a few other options.
- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed.
- Create a "Worksheet_Calculate" event procedure, which runs whenever a value on the sheet is updated. The caveat here is that there is no "Target" cell in a "Worksheet_Calculate" event procedure. Excel has NO idea what cell was updated, just that one was somewhere on the sheet. So if you use this option, you cannot limit it to just the cell that was changed.
 

steve400243

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Worksheet_Change event procedures do NOT run when cells are changed via formula!
They only run when cells are manually updated (manual entry, copy/paste).

If cells A3 and A4 contain formulas, then you have a few other options.
- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed.
- Create a "Worksheet_Calculate" event procedure, which runs whenever a value on the sheet is updated. The caveat here is that there is no "Target" cell in a "Worksheet_Calculate" event procedure. Excel has NO idea what cell was updated, just that one was somewhere on the sheet. So if you use this option, you cannot limit it to just the cell that was changed.

"- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed" How would this be written?

Here is the formula I am using in Column A.
VBA Code:
=IF(D3="","",IF(F3="","Received","Shipped"))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
"- If the formulas contain references to other cells that are being updated manually, write your code to run when those values are manually changed" How would this be written?

Here is the formula I am using in Column A.
VBA Code:
=IF(D3="","",IF(F3="","Received","Shipped"))
So if you were to use a Worksheet_Change code, it looks like you want your code to run off of changes to columns D and F, so you would structure very similarly to how we did the first one, with columns C and E.

Note that we can trigger it to run off of changes to columns D and F, but still look at the value in column A.
 

steve400243

Active Member
Joined
Sep 15, 2016
Messages
377
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

So if you were to use a Worksheet_Change code, it looks like you want your code to run off of changes to columns D and F, so you would structure very similarly to how we did the first one, with columns C and E.

Note that we can trigger it to run off of changes to columns D and F, but still look at the value in column A.
I think if the code was triggered to run just off Column A when changed to "Shipped" would be ideal. Would I change the code to something like this? Thanks a lot for your assistance my VBA Skills are still in the beginner. So much to learn.

VBA Code:
 If Target.Column = 1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
I think if the code was triggered to run just off Column A when changed to "Shipped" would be ideal. Would I change the code to something like this? Thanks a lot for your assistance my VBA Skills are still in the beginner. So much to learn.

VBA Code:
 If Target.Column = 1
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
 

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
"They only work on manually updated data." I'm sorry but I missed that part when reading your post the first time. I will work with what you have provided a bit later today but I may reach back out for assistance on this post so I will keep it open for now. Thank you again for your time, and skills.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,148,177
Messages
5,745,194
Members
423,932
Latest member
pablo2

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