VBA to copy specific columns from one sheet in to specific columns on another sheet based upon certain criteria

DB67 · Mar 31st 2020

Hi,

I'm new to writing macros and could really use your help - see the attached file as an example.

The "Overall Service" column has drop down list with the following options: G, A, R.

If users select A or R in this column then the following must happen:

- "Name of Client" must be copied from sheet 1 column C on to sheet 2, column B

- "Location" must be copied from sheet 1 column D on to sheet 2, column C

- "Overall Service" must be copied from sheet 1 column F on to sheet 2, column D

I have populated row 3 on sheet 2 as an example of what I need it to look like.

Any help is greatly appreciated and if you have any recommended sources for learning macro writing / VBA programming then that would be extremely useful.

Thanks,

Darren.

**StephenR** · Mar 31st 2020

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

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

Display More

DB67 · Mar 31st 2020

I've followed the steps you mentioned however nothing has copied across to sheet 2 (not sure if there's anything else I need to do after those steps?). I've attached the updated sheet for you to see.

That's a good point about repeats. It would be very rare that a user would have to change their initial selection unless they accidentally chose the wrong one initially. If they did for example choose A accidentally, but then changed it back to G, would the data from the initial selection still remain on sheet 2? If so, do you know any way around this?

Thanks very much for your help!

DB67 · Apr 1st 2020

After playing about with the code I managed to get it to copy across the data to the second sheet however I've got the repeats problem.

Let me know if you think of a way to solve this part.

Thanks!

**StephenR** · Apr 1st 2020

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.

DB67 · Apr 1st 2020

What I was thinking of doing was using a command button on sheet 2 to run a Vlookup of the "Overall Service" rating on sheet 1 and then delete the row on sheet 2 if the Vlookup was false. That way once all users have input their data, this command button will remove any incorrect rows / repeated rows.

So for each client on sheet 2, the macro would lookup column D from sheet 2, in Column F on sheet 1. If it matches sheet 1, then the row of data on sheet 2 will remain. If it doesn't match, then the row of data will be deleted.

Do you happen to know the coding for this? Or a thread which discusses this?

I've attached an updated file - thanks again!

**StephenR** · Apr 1st 2020

So there is only ever one row for each client?

DB67 · Apr 1st 2020

There should be yeah. The only way there would be more than one is if theres a duplicate / repeat. Therefore if that's the case, the only one that should remain is the one who's overall service matches the overall service rating on sheet 1.

Hope that makes sense.

**StephenR** · Apr 1st 2020

But on sheet1 there is only one row per client?

DB67 · Apr 1st 2020

correct

**StephenR** · Apr 1st 2020

Try this revised code.

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

Display More

DB67 · Apr 1st 2020

its not working unfortunately!

Edit: sorry I should have been more specific. I've replaced the code on sheet 1 with that revised code. The data is still being copied over to sheet 2 however repeats are not being removed.

**StephenR** · Apr 1st 2020

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?

**StephenR** · Apr 1st 2020

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?

DB67 · Apr 1st 2020

1) I've tried changing Orange to R on sheet 1 however it is still staying as Orange on sheet 2. However the client name should never change anyway. The only thing that should change is the overall service rating. So if the client manager for Orange input "A" for their service rating on sheet 1 but then subsequently changed it, I would need the row on sheet 2 to be updated with "R" if the service was changed to "R", or deleted if the service was changed to "G".

2) its not possible to start from scratch I'm afraid. I'll give you some background...

Sheet one will contain one line per client. Client managers will fill out the line relating to their client(s). If the "Overall Service" rating is "A" or "R", they will have to provide additional details on sheet 2 as to why it is "A" or "R". Therefore if 10 client managers have already input information onto sheet 2 at the point where there is a repeat, starting from scratch would remove all of the information that they have input on sheet 2.

Let me know if you need me to clarify any of that.

**StephenR** · Apr 1st 2020

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.

DB67 · Apr 1st 2020

1) Don't know what was happening with m excel but when I first tried it it wasn't updating. Just tried it now and it has changed from R to A (and vice versa) so thanks for that! That being said, if I change it to G, nothing happens on sheet 2. In this scenario where a line has been changed from A/R to G, I would need the row removed from sheet 2 - is that possible to code into the macro?

2) Each month a new report is created for that month with B:D already populated, Client Managers go in and populate E:T. Each new report has 1 line per client and currently if a client as "A" or "R" for "Overall Service" in sheet 1, the Client Manager must go onto sheet 2 and populate everything themselves. The purpose of the macro was to save time on populating all the columns on sheet 2, and to also ensure any clients with "A" or "R" on sheet 1 are recorded on sheet 2 with additional details and are not missed/forgotten about.

**StephenR** · Apr 1st 2020

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.

DB67 · Apr 1st 2020

Yeah sheet 2 will start off blank when the report is created initially.

Aw perfect, I think that would sort everything then? As all the relevant columns on sheet 2 are automatically populated, and if a user changes their selection then it will be updated on sheet 2 or removed if they change their selection to G.

**StephenR** · Apr 1st 2020

Code

Private 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

Display More

VBA to copy specific columns from one sheet in to specific columns on another sheet based upon certain criteria

Participate now!

Share

Tags