Tweak Copy/Paste Special Macro based on conditions

[kingofcamden]

Hi all,

I found the script below (which works great), however, I would like to tweak it to:

1) Check against a value on worksheet "settings" in cell C3 (this is a dropdown where you can select from a range of values)
2) Then, depending on the value selected, special copy/paste from various worksheets and cells

Please Login or Register  to view this content.

I've attached an example spreadsheet. Hope this helps!

In total, there will be approx. 100 cells to copy over (example only holds 1) for each value you can select. So the script should accommodate for that. E.g. if value Template 1 is selected, copy 100 cells from worksheet Template1. If Template 2 is selected, copy 100 cells from worksheet Template2.

The copy/paste should be special (as in the existing script) so can't work with formulas here, I'm afraid.
Not quite sure where to start with this so would appreciate any help!
Many thanks!

[Mumps1]

Start by modifying the tab names for "Template1" and "Template2" to "Template 1" and "Template 2" so that there is a space before the number. This will make the sheet names the same as those in the drop down list. Then copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Template" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in C3. You can delete your current macro as it is no longer necessary.

Please Login or Register  to view this content.

[kingofcamden]

Awesome!!! That works perfectly Thank you so much!

Only question I have, if there are more values/cells to copy over (i.e. up to 100), what would I need to tweak?
Example: if Template 1 is selected, then copy/paste cell C3 into C6 and C4 into C7 and... all from worksheet Template 1 (or Template 2 etc. depending on what is selected). In the end I'll have approx. 20 values in the dropdown and therefore 20 worksheets to copy up to 100 cells over for each.
Hope that makes sense?

Many thanks for your help!

[Mumps1]

Try the attached file. I had to unmerge the cells in the "Template" sheet. You should avoid using merged cells if at all possible because they cause havoc for Excel macros.

[kingofcamden]

Awesome! That seems to do the trick! Thanks so much! I owe you a cyber beer!

[Mumps1]

You are very welcome. If I'm ever in London, I'll take you up on that.