Could you not sort the data before emailing?
Posts by StephenR
-
-
-
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Column <> 6 Or Target.Count > 1 Then Exit Sub With Sheet2 Set r = .Range("B:B").Find(What:=Target.Offset(, -3).Value, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) 'look for client on sheet2 If Target.Value = "A" Or Target.Value = "R" Then If r Is Nothing Then 'client not found on sheet2 With .Range("B" & Rows.Count).End(xlUp)(2) .Value = Cells(Target.Row, "C").Value 'Name of Client .Offset(, 1).Value = Cells(Target.Row, "D").Value 'Location .Offset(, 2).Value = Cells(Target.Row, "F").Value 'Overall Service End With Else r.Offset(, 2).Value = Target.Value End If ElseIf Target.Value = "G" Then 'remove from sheet2 if G on sheet1 If Not r Is Nothing Then r.EntireRow.Delete shift:=xlUp End If End With End Sub
-
-
1) Perhaps we are at cross purposes. On sheet 2 there is a line for Orange and it reads R (say). If you change the Orange line on sheet1 to A then it will change the R on sheet2 to A. It will not add a new line on sheet2 so there will only ever be one line for each client.
2) That said, if you already have multiple lines for each client the code will not currently remove surplus ones. If you haven't used code before for this, how are these multiple lines appearing - have they been manually copied and pasted?
Depending on your answer to (2) an easier approach would be to have a line for each client on sheet2 and just use Lookup formulas to update when sheet1 is updated.
-
-
You'll need to be more specific.
See the attached. If you change Orange to R it will update on sheet 2 but not add a new row.Macro Example_v.3.xlsm
Edit: if there are repeats when you first run the code then they will not be removed. Is it possible for you to "reset" sheet2 and start from scratch?
-
Try this revised code.
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Column <> 6 Or Target.Count > 1 Then Exit Sub If Target.Value = "A" Or Target.Value = "R" Then With Sheet2 Set r = .Range("B:B").Find(What:=Target.Offset(, -3).Value, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) If r Is Nothing Then 'client not found on sheet2 With .Range("B" & Rows.Count).End(xlUp)(2) .Value = Cells(Target.Row, "C").Value 'Name of Client .Offset(, 1).Value = Cells(Target.Row, "D").Value 'Location .Offset(, 2).Value = Cells(Target.Row, "F").Value 'Overall Service End With Else r.Offset(, 2).Value = Target.Value End If End With End If End Sub
-
-
-
-
Try this in the worksheet module of sheet1. Right-click the sheet tab, view code and paste the code.
Not sure how you handle repeats? What if a cell is changed from A to R or A to G etc?
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 6 Or Target.Count > 1 Then Exit Sub If Target.Value = "A" Or Target.Value = "R" Then With Sheet2.Range("B" & Rows.Count).End(xlUp)(2) .Value = Cells(Target.Row, "C").Value 'name .Offset(, 1).Value = Cells(Target.Row, "D").Value 'location .Offset(, 2).Value = Cells(Target.Row, "F").Value 'service End With End If End Sub
-
-
You can slice arrays, in a manner of speaking, in VBA, viz:
Code
Display MoreSub x() Dim Arr(1 To 5, 1 To 4) As Variant, Arr1 As Variant Dim i As Long, j As Long 'this bit is just to populate the first array so you won't need For i = LBound(Arr, 1) To UBound(Arr, 1) For j = LBound(Arr, 2) To UBound(Arr, 2) Arr(i, j) = i * j Next j Next i 'this says just take the first 3 rows (and all 4 columns) Arr1 = Application.Index(Arr, Evaluate("ROW(1:3)"), Array(1, 2, 3, 4)) End Sub
-
Code
mysheet.Rows(2 & ":" & mysheet.cells(Rows.Count, "A").End(xlup).Row).Cells.Replace what:=fnd, Replacement:=rplc, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False
Just remove the Select, which is almost always redundant and inefficient anyway.
-
You have posted this on another forum without providing a link, which is counter to Ozgrid rules.
Please show consideration to people who help here by posting links to all forums where you have asked this question.
-
-
-
-
Are these averages based on data in the Summary sheet? Which columns are being averaged? Perhaps you could post a sample workbook.