Posts by DB67

Hi Stephen,

Sorry to bother you again!

I was wondering if you could help with one last piece of code for this workbook?

Each month, most clients will have all of their stats green for each department. Therefore instead of both users having to go in and fill in each individual department green for that client, I was hoping to create a command button which would populate all of the relevant cells with the letter "G" based upon the options chosen from two drop down lists.

What I would need is:

- When a user selects "Operations" from the first drop down list and selects a client, the command button will input the letter "G" in columns: G, I, K, M, O, Q, and S for that client.

- When a user selects "Client Management" from the first drop down list and selects a client, the command button will input the letter "G" in columns: H, J, L, N, P, R, and T for that client.

I've attached my updated sheet with the first row completed as an example if a user was to select "Operations" and "Apple" then hit the command button.

Many thanks!!

Great, will do!

Thanks again.

Works perfectly, thanks so much!

Are there any resources that you'd recommend to learn VBA coding?

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.

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.

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.

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.

correct

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.

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!

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!

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!

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.