Automatically copy cells to another worksheet on condition

[maxd]

Hi there. I'm reasonably new to excel and have encountered a macro conundrum which I hope you can help me with. Unfortunately, I can't find this specific issue on the forums.

I have a worksheet "BD" where I enter data in the cells of columns A thru Q. In column "T", I have a conditional entry "A,P,O,-". When I select "A", I would like the contents of the cells in columns A,D and Y of that row to be copied into my other worksheet "Bid Document".

However, when I select "O" or "-" then I would like that entry to be automatically removed from the worksheet "Bid Document".

Finally, when I select "P", I want these entries to be copied over also, but to start after those entries which were copied when selecting "A".

I sincerely hope that you can help me out.

Many thanks,
Max

[jaslake]

Hi maxd

If you'll upload a dummy file of what you have and what you'd like it to be, I'll be glad to look at this with you. Otherwise, I'll need to dummy up a file for testing and I'd only be guessing.

John

[soci0path]

I'm doing something similar.

[maxd]

Many thanks for the v. quick response guys. Much appreciated! I'll get the file stripped down for easy of use and upload what I have shortly. :-)

Best,
m

[foxguy]

Hi m;

When you click "O-", how will the macro know which row on Bid Document to delete? Will Columns A, D, & Y still be the same value as they were when you clicked "A"?

When you click "A", do you want to copy Columns A, D, & Y ONLY. Or can a 4th column be added so that when you click "O-" the macro can find the correct row?

When you click "P" a 2nd time, do you want to replace the row that was added when you clicked "P" the 1st time, or add another row?

When you click "O-", do you also want to delete the rows that were added when you clicked "P"?

[maxd]

Hi Guys,

I've uploaded two JPEGS of my worksheet so that you can see what I have and what I need.

As you can see on the image 'BD.jpg' in Column 'T' (Labeled "Cat") there is a drop down conditional list with values "A, P, O" and "-".

When the value is "A" for any given Row, the contents of the respective cells in the columns named "Scene", "Shot", "Description", "Notes", and "Cost (exc Assets)" for that specific Row are copied automatically to the other work sheet "Bid Document" (Bid Document.jpg).

When this value changes to either "O" or "-", the contents are removed from the worksheet 'Bid Document' (or do not appear at all). i.e only when the Values "A" or "P" are selected do the cells appear on the other worksheet.

Let me know if this makes sense? :-)

Many thanks again!
Max

[jaslake]

Hi maxd

If you'll upload a dummy file of what you have and what you'd like it to be, I'll be glad to look at this with you. Otherwise, I'll need to dummy up a file for testing and I'd only be guessing.

Pictures aren't much help.

John

[maxd]

Hi John,

No problem. Here's a stripped down version of the Excel doc. Let me know if you have any questions :-)

Many thanks,
Max

[jaslake]

Hi Max

In your original post, you indicated

when I select "P", I want these entries to be copied over also, but to start after those entries which were copied when selecting "A".

So, if you were to change a Cat value from "A" to "P", you would have two records for that item in "Bid Document", the original "A" record and the newly created "P" record . Correct?

Will you be changing a Cat Value from "P" to an "A"? If so, what happens to the "P" record in "Bid Document"? Will you now have two "A" records in "Bid Document"?

Will you be changing a Cat Value from "P" or "A" to a "-"?

Please forgive all the questions. It's difficult to write code unless one understands the process.

FYI...to make this work (if indeed it does work), I've needed to add a hidden column in "Bid Documents". If that gives you heartburn, let me know 'cause I don't know how else to do it.

John

[maxd]

Hi John, many thanks for the speedy reply :-)

Good questions! Firstly, the idea behind all this is that the worksheet 'Bid Document' is a stripped down summary of the pertinant data for a client. The page can then be used as a PDF or printed out etc.

To answer your questions, the values 'A' and 'P' stand for 'Actuals' and 'Potentials' respectively. So if I changed the value from one to the other, there should be no duplicates of the same entry in the 'Bid Document'.

So for example if I have an entry: "Shot 1, scene 1 etc etc" which currently has a CAT value 'A' and I need to change it to 'P', then that entry moves down below all the 'A' entries. And vice-versa - if an entry currently has a value of 'P' and I change it to 'A', then that entry moves up above all the 'P' entries.

If a CAT value is, or I change it to either 'O' (Omit) or '-' (No Catagory), then that entry should not appear in the 'Bid Document'.

In other words, only entries with a CAT value of 'A' or 'P' should appear in the 'Bid Document'.

Does that make sense? Thanks so much John - really appreciate this :-) Feel free to add an extra column if necessary - (I can always hide it)

Best wishes,
Max

[jaslake]

Hi maxd

Try the code in the attached workbook and see if it's even close to what you're looking for.

I'm not sure of this

So for example if I have an entry: "Shot 1, scene 1 etc etc" which currently has a CAT value 'A' and I need to change it to 'P', then that entry moves down below all the 'A' entries. And vice-versa - if an entry currently has a value of 'P' and I change it to 'A', then that entry moves up above all the 'P' entries.

I've got a sort procedure in the code but I don't know if the sort accomplishes the above.

Let me know of issues.

John

[maxd]

Hi John,

Thanks so much for that. Wow! You have the principle exactly :-)

There are a few issues with the code which I've just had a look at. Firstly, if I turn all the entries back to '-', something strange happens to the first line entry (Row 8) on the 'Bid Document'. Firstly, there is a cosmetic change - the row height and font size and colour of the cells inherit the values of the row above it - Row 7. The same appears to happen for all of the entries strangely.

Secondly, if I then turn the first line entry back to 'A' and then back to '-', the entry is not deleted from the 'Bid Document'.

Finally, I need all the entries in the 'Bid Document' to remain 'above' the 'Sub Total' cell (Row26). Currently, if I continue to add entries, they eventually overwrite Row 26 and beyond. Essentially if there are 10 'A' or 'P' entries in the worksheet 'BD' then there should only be 10 Rows with entries in the worksheet 'Bid Document', and no emply rows if you follow?

Once again, thanks so much for this great help!
cheers
m

[jaslake]

Hi maxd

I think this issue is resolved

if I turn all the entries back to '-', something strange happens to the first line entry (Row 8)

I couldn't duplicate this issue

if I then turn the first line entry back to 'A' and then back to '-', the entry is not deleted from the 'Bid Document'.

This issue I believe is resolved

if I continue to add entries, they eventually overwrite Row 26

Regarding this

no empty rows if you follow

There must be one(1) empty row in "Bid Document".

I've destroyed much of the formatting in "Bid Document". Test the functionality then we can deal with formating (hopefully).

John

[maxd]

Hi John,

That is perfect! Thank you so much! I really appreciate all your hard work as this will help me a great deal :-)

Thanks again, and best wishes,
Max

[jaslake]

Hi maxd

Glad it works for you. If you're satisfied, please mark your post as solved. Style points will be appreciated if you feel appropriate (click on scales).

John