Posts by StephenR

Could you not sort the data before emailing?

Hang around places like Ozgrid?!

There are tons of websites with tutorials, the best way, in my experience, is to automate your own bits of work and ask questions when you get stuck, as you have here. If you hunt around Oz you might find a thread on useful websites etc.

So if a new report is done each month won't sheet2 start off blank?

Can change the code so that if G on sheet1 the row is removed from sheet2.

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.

In case you don't see my edit above,

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?

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.

Private 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

But on sheet1 there is only one row per client?

So there is only ever one row for each client?

Glad you managed to get it working.

For repeats, you'd need some way of identifying them. So, a combination of name and location for example. Then you could do a COUNTIFS perhaps to check not greater than zero which means it already exists. If you had a unique ID that would be ideal.

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?

Private 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

Do you have named ranges "ComboDay" and "ComboMonth1" etc?

You can slice arrays, in a manner of speaking, in VBA, viz:

Sub 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

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.

Not sure, I think I should be able to but I can't at the moment. In the meantime, please can you add code tags to your original post? They are currently attached in the first bit of the post which doesn't need them.

You've put this in the Excel and Access forum, but it looks like a straight Excel question?

Are these averages based on data in the Summary sheet? Which columns are being averaged? Perhaps you could post a sample workbook.