VBA to copy Named Ranges

Hi. I need some help simplifying some VBA. I am including a sample spreadsheet.

I have 7 separate named ranges that are all formatted the same, with some cells protected and some cells merged.

I want to be able to copy the data (values only) from one of the named ranges into any (or all) of the named ranges without having to write a separate sub for every combination. Can this be done?

For instance, I want the user to be able to click on a button below the area to be copied and they be asked where to copy it, giving check boxes, or some other way to choose where to put the copied data.

I used the following code to copy one range to another specific range, but I would like to take that further if possible. Any help is appreciated.

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

I wouldn't do this with a whole bunch of buttons, I would do it with a user form. See attached, click the button in the upper left corner.

Also, I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Also you should get rid of merged cells. Nothing but trouble and your sheet can be formatted to look the same without them.

Thank you! I am a novice, but this would be so much cleaner. I will use a userform instead.

Is it possible to be able to select more than one Tier to copy (or clear) to at a time? As written, I can only choose 1 at a time.

Originally Posted by 6StringJazzer

You can't copy from more than one tier at a time. Do you mean copy from one tier to multiple other tiers...?

For clearing, the option buttons can be converted to checkboxes, and the code would have to be changed accordingly.

Yes, I meant copy from 1 tier to multiple tiers. Thanks for your help.

Originally Posted by 6StringJazzer

I'll try to find a few minutes to make those updates.

Wow. Thank you. I really appreciate it.

Originally Posted by 6StringJazzer

Here you go!

It prevents you from copying a tier back onto itself. It will not notify you if you have no checkboxes selected; it just won't do anything.

Thank you so much! This is great. It's exactly what I need.

Originally Posted by 6StringJazzer

Here you go!

It prevents you from copying a tier back onto itself. It will not notify you if you have no checkboxes selected; it just won't do anything.

Hi again.

I apologize for bothering you again, but I realized after I marked the string as solved that I had made a mistake in my sample that I provided to you. I was hoping that I could figure out the code enough to correct it myself, but I was unsuccessful.

In my original sample I did not have enough lines to represent my actual spreadsheet and I had 7 named ranges. I should have had 14 named ranges. 7 for the copy function and 7 different ones for the clear function.

I was hoping that I could just change the names of the ranges in the code, but I cannot find where it references the named ranges.

I have scoured the code you provided, but I must be missing something.

Can you help me again?

Originally Posted by 6StringJazzer

which ones are which?

Sorry. The ones that end in "ALL" go with the Clear set. The ones with just the Tier_# go with the Copy set.

Originally Posted by 6StringJazzer

Here's an update. The change is in the Clear sub where the name of the tier is determined based on the checkbox. I also added some comments to help clarify what the code does.

Thanks again! That does exactly what I need. I really appreciate the extra explanation of the code.