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

  • 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.

  • 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?

  • 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!

  • 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.

  • 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!

  • 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.

  • Try this revised code.

  • 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.

  • 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.

  • 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.

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!