VBA/Formula Combo returning reference error (#Ref) Excel 2016

[poptart1108]

Hi all,

I created a workbook with 7 sheets (a master and one for each person on my team). The sheet is meant to track our commissions. I used a VLook Up Formual to match the product with the commission rate (the table is on sheet 2). Everything works on the master sheet.

However, I also put in a VBA code to copy an entire row of the master sheet to one of the remaining 6 depending on which team member sold the piece. When it copies the row, it retruns a #ref error in columns with any formula, but no errors show on the master sheet. Any ideas? See VBA code below:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Me.Range("A:A")).Cells
If C.Text = "Adkins" Then
C.EntireRow.Copy Worksheets("Adkins").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
End If
If C.Text = "Wallace" Then
C.EntireRow.Copy Worksheets("Wallace").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
End If
If C.Text = "Sachs" Then
C.EntireRow.Copy Worksheets("Sachs").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
End If
If C.Text = "Leary" Then
C.EntireRow.Copy Worksheets("Leary").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
End If
If C.Text = "Smith" Then
C.EntireRow.Copy Worksheets("Smith").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
End If
If C.Text = "Frazier" Then
C.EntireRow.Copy Worksheets("Frazier").Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
End If
Next
End Sub

 

[AlphaFrog]

The copied formulas have references to cells that are no longer valid on the destination sheets.

This will copy the Values and not the Formulas. It's also a little more concise.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] C         [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]If[/COLOR] Intersect(Target, Me.Range("A:A")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] C [COLOR=darkblue]In[/COLOR] Intersect(Target, Me.Range("A:A")).Cells
        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] C.Value
            [COLOR=darkblue]Case[/COLOR] "Adkins", "Wallace", "Sachs", "Leary", "Smith", "Frazier"
                Worksheets(C.Text).Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow.Value = C.EntireRow.Value
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    [COLOR=darkblue]Next[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]