If, then, copy programming

[dodom75]

I have attached a worksheet as my example.

I have been trying to build code and I am a newbie when it comes to this.

I have a sheet that is user entered (Criteria). I need to build a macro/code that says if they choose "Yes" to any of the Products on the criteria sheet, then copy all rows that have an X in them that are in the corresponding column on the Master Template. To make it even more complicated I only want to copy 4 of the columns (ID, Task, Share with Client and Accountable).

I know if they choose multiple columns then I could have duplicate rows, but once all the rows are copied I was going to build a remove duplicates macro. However, if there is anyway of preventing duplicates with the original I am open for any assistance.

[rylo]

Hi

See how this goes. I've had to update your defined name YN as it was referencing a sheet that no longer exists in the workbook.

rylo

[dodom75]

Hi Rylo,

Thank you so much. I have attached the full version of the form. The information you sent me worked perfectly.

So I have a few more questions, if you don't mind answering? I am taking some classes in August so hopefully I will understand this by then.

How can we bring over the title rows (yellow lines above the sections) and within the larger sections there are break lines. Is there a way to bind the macro to say for section if Row 3-14 is copied/selected automatically pull row 2 and the formatting from the master template?

Then how do we get it to sort in the correct order?

Again, thank you so much for the other information, I sincerely appreciate it.

[rylo]

Danielle

Try this.

Please Login or Register  to view this content.

In Master Template!I461, you have an x in your heading row. Running this code on your latest example file will bring this heading over 2 times. I'm guessing that this X shouldn't be in that cell.

rylo

[dodom75]

Hi Rylo,

It worked perfectly, thank you. However, is there anyway to make that formatting copy for column E, F, G, H, I? it only copys to the cells we copy over. Or, is there a way to make E, F, G, H, I just copy the color from column D?

Thanks again for all your help. I really appreciate it. Have a good evening.

[rylo]

Danielle

Change the with block at the end to be

Please Login or Register  to view this content.

rylo

[dodom75]

Hi Rylo,

Thank you again. When I ran the code you posted it highlighted row e-h in yellow instead of copying the formating of column B. So I tried changing the range reference to("b" & Range("b60000") code below, but now I get an error run-time error 1004. Any ideas?

Please Login or Register  to view this content.

[dodom75]

I was able to get this to work as a separate macro below, but when I try to put them together it is not copying all the way down.

Please Login or Register  to view this content.

[VBA Noob]

dodom75,

You have being asked before I believe to read the forum rules. If you had read them (Forum rules below) you would know cross posting without providing the link is not permitted

Please add the link now if you wish to continue posting here

VBA Noob

[dodom75]

Not sure which one is considered cross posting but here are my links:

I think this is it:
http://www.mrexcel.com/forum/showthread.php?t=329481

[rylo]

Danielle

In your request, you asked for the color to be copied over. Now you are talking about the formatting. What exactly do you want to copy from where. Go back to your example workbook, and give exact references for the from and to position, and specify exactly what you want to transfer (all formats, borders, interior colors etc).

rylo

[dodom75]

Hi Rylo,

I ended up using a total separate macro and having this one call it at the end. It works. If there is a cleaner way to do it, I am all ears. Below is the macro I am calling.

Thank you for all your hard work on this one. I really appreciate it.

Please Login or Register  to view this content.

[rylo]

Danielle

I get it now - you only want to color columns E-H for the header rows, not all the rows.

There is nothing wrong with running separate code, but if you want to include it with actions to bring across the header data then....

Please Login or Register  to view this content.

rylo

[dodom75]

Thank you for all your help. I appreciate it.