Need help automatically moving Source Data from one tab to others

[PCosta87]

Hi,

Here you go... incomplete however.

1. FIP and MAP should also look at Effective Date to drive policies to either “02 Products or Older” or “No Rider Restrictions”.

I couldn't find any FIP or MAP. Can you add examples for these so I can test for them?

New Steps to occur prior to the existing steps

Done.

On the “BoB” tab I am hoping to add three columns. Is it possible to make the location of our columns dynamic on this page to allow them to land in a different location? If not this is not a huge deal I would just need to figure out how to add the three new columns in their current locations. (“Rebalance Running (Y/N)”, “Phone Authorization” and “DCA Running Y/N”). The addition of these obviously moved the location of “Available Funds Tab” column to M but nothing else we use was relocated.

Code updated to account for the new position of the columns.
If moving them is often required, you should assign names to the columns (named ranges) so that they can be used in the code instead of referencing the columns.

Is it possible to auto hide all tabs that no information is being driven to?

Done.

Once info is driven to each page I manually go through and color code the policy number only when the policy number contains a policy scheme located in the key on that page. Is there a way to make this happen automatically? Everything else should remain white as there are no restrictions. The only other thing is on both RIC 1.2 tabs I highlight the policy number blue for any policies that have an “O” in the “Allocation method” column. This might be a lot but I’d love to get this automated as well.

If step three is possible is it then possible to auto hide any columns and rows that do not apply(columns and rows that reference a policy scheme that is not listed)?

I added a VLOOKUP in here using the “Rider Translator tab. This turns a code into an actual name that we use for the macro. Can we auto add that to the “Rider Type” Column allowing us to just paste several rows of date and have that translate automatically? I understand we will still have to go in and re paste over the top to change from a formula to values unless you have a better idea?

Could you please explain it better.
How will the color codding work exactly?
How do I know what to hide?
What do you want to translate automatically? VBA can input whatever formula you want into a cell so that can be done automatically. I just don't understand what is it that you want to do exactly.

Can you try to explain in greater detail, providing examples whenever possible?

Thanks

 

[Dustin Hoffman]

Hi,

Here you go... incomplete however.


I couldn't find any FIP or MAP. Can you add examples for these so I can test for them?

 

[Dustin Hoffman]

Silly me I used the acronym. What I meant was anything with the below two names:

Family Income Protector
Managed Annuity Program

Both of these should automatically reference the effective date to determine either 02 Products or Older or no Rider Restrictions. This should be done after the new steps you already put in as some of these will be correctly driven to WRL and other tabs.

 

[Dustin Hoffman]

"New Steps" Done.

This is great! Thank you. I also added Rider Type, Rider Base and Rider Start date to a few of the tabs they weren't already located but it appears as though it only drove that detail to the WRL tab. The third policy listed from the bottom on the 02 Products and older tab is an example of one that would have brought over a rider name ext. This is also one that should drive to "No Rider Restrictions" once we get the effective date as the point of reference.

 

[Dustin Hoffman]

Hi,

Code updated to account for the new position of the columns.
If moving them is often required, you should assign names to the columns (named ranges) so that they can be used in the code instead of referencing the columns.

 

[Dustin Hoffman]

Thank you. I'm not sure I understand what you mean by assigning name ranges. How can I do that?

Also it appears on the RIC 1.2 MAY.09 tab Invesstment method column is not pulling in the correct info. It appears to be pulling in either column I or L from the "BoB" tab rather than it's new location of "J".

 

[Dustin Hoffman]

Hi,

Done.

Could you please explain it better.
How will the color codding work exactly?
How do I know what to hide?
What do you want to translate automatically? VBA can input whatever formula you want into a cell so that can be done automatically. I just don't understand what is it that you want to do exactly.

Can you try to explain in greater detail, providing examples whenever possible?

Thanks

 

[PCosta87]

Silly me I used the acronym. What I meant was anything with the below two names:

Family Income Protector
Managed Annuity Program

Both of these should automatically reference the effective date to determine either 02 Products or Older or no Rider Restrictions. This should be done after the new steps you already put in as some of these will be correctly driven to WRL and other tabs.

Here, I believe this shall do it

 

[PCosta87]

Thank you. I'm not sure I understand what you mean by assigning name ranges. How can I do that?

Like this:

But, on second thought, I realize it wouldn't help much since I would still have to change the offsets manually each time.
It would have been much more useful if I had known this from the start... right now, coding it so you can move the columns around would require me to rewrite most of the code, unfortunately

 

[PCosta87]

Also it appears on the RIC 1.2 MAY.09 tab Invesstment method column is not pulling in the correct info. It appears to be pulling in either column I or L from the "BoB" tab rather than it's new location of "J".

I see...
That is because you added "Rebalance Running (Y/N)" before the "Investment Method"... if possible keep "Investment Method" right after "Rider Start Date", otherwise I will have to revise the code to account for that, which I missed when I did the previous update.

 

[PCosta87]

"New Steps" Done.

This is great! Thank you. I also added Rider Type, Rider Base and Rider Start date to a few of the tabs they weren't already located but it appears as though it only drove that detail to the WRL tab. The third policy listed from the bottom on the 02 Products and older tab is an example of one that would have brought over a rider name ext. This is also one that should drive to "No Rider Restrictions" once we get the effective date as the point of reference.

This is a bit confusing so I will leave it be for now... if the current updated version of the code isn't getting this right I will then fix it.
Test the most up-to-date file and let me know.

 

[Dustin Hoffman]

Hiding the Tabs no info is being Driven to: Perfect! thank you.

Color Coding Each Tab. I've attached an example where I updated the following tabs:

"02 Products and Older" - This one and Misc both work a little different than the rest since there are so many possibilities. On the right there are several columns though outline what's available for each policy scheme type. For example I highlighted all of the LK2 policy numbers the same color green and then I copy and pasted the check boxes over to the left for only the policy schemes that apply. The schemes are all identified in (parenthesis) within each header on the right hand side columns. Anything not identified on the right should remain white as they have all of the funds on the left available to them.

"WRL" & "ML & Dreyfus" - These you don't have to worry about yet as I am not done building all of the options so I will manually color code these for a while.

"No Rider Restrictions" I've got similar columns on the right here but I also have a key for this and the other tabs. Both list out the policy schemes that I want highlighted in each respective color. Example: I have one policy that "contains" PPM so I color coded that to match the yellow in the key and the corresponding hidden column on the right. Example 2: I have several policies that contain either PM8 or PM3, since they share a color I color coded them both to match that color in the key. Example 3: Since I did not have any policies that contain "LST" I hid column "H" and Row "7".

"RIC 1.2 MAY.09" - Similar to No Rider Restrictions, since I did not have and LK3 I hid the corresponding row and column in the key. I color coded a couple policy numbers orange to match the PM8 listed in the key. Lastly. I manually added "Open" to two policies in Column "O" and highlighted those to match the "Open" listed in the key.

Hope that all makes sense

Thank you again for everything.

 

[Dustin Hoffman]

Like this:
View attachment 42004
But, on second thought, I realize it wouldn't help much since I would still have to change the offsets manually each time.
It would have been much more useful if I had known this from the start... right now, coding it so you can move the columns around would require me to rewrite most of the code, unfortunately

This is just fine. Thank you. Most of the time these criteria will be all we need. We can also add info to the columns to the right later so adding these new three will work perfect. Thank you

 

[PCosta87]

Hiding the Tabs no info is being Driven to: Perfect! thank you.

Color Coding Each Tab. I've attached an example where I updated the following tabs:

"02 Products and Older" - This one and Misc both work a little different than the rest since there are so many possibilities. On the right there are several columns though outline what's available for each policy scheme type. For example I highlighted all of the LK2 policy numbers the same color green and then I copy and pasted the check boxes over to the left for only the policy schemes that apply. The schemes are all identified in (parenthesis) within each header on the right hand side columns. Anything not identified on the right should remain white as they have all of the funds on the left available to them.

"WRL" & "ML & Dreyfus" - These you don't have to worry about yet as I am not done building all of the options so I will manually color code these for a while.

"No Rider Restrictions" I've got similar columns on the right here but I also have a key for this and the other tabs. Both list out the policy schemes that I want highlighted in each respective color. Example: I have one policy that "contains" PPM so I color coded that to match the yellow in the key and the corresponding hidden column on the right. Example 2: I have several policies that contain either PM8 or PM3, since they share a color I color coded them both to match that color in the key. Example 3: Since I did not have any policies that contain "LST" I hid column "H" and Row "7".

"RIC 1.2 MAY.09" - Similar to No Rider Restrictions, since I did not have and LK3 I hid the corresponding row and column in the key. I color coded a couple policy numbers orange to match the PM8 listed in the key. Lastly. I manually added "Open" to two policies in Column "O" and highlighted those to match the "Open" listed in the key.

Hope that all makes sense

Thank you again for everything.

I think the attachment is missing.
Please upload when you can.

 

[Dustin Hoffman]

I see...
That is because you added "Rebalance Running (Y/N)" before the "Investment Method"... if possible keep "Investment Method" right after "Rider Start Date", otherwise I will have to revise the code to account for that, which I missed when I did the previous update.

Darn Unfortunately the order automatically pulls from another internal report and making changes to that can take a while. If you are able to revise the code it would be great but if you can't we can just manually move the data before pasting it in.

 

[Dustin Hoffman]

I think the attachment is missing.
Please upload when you can.

Here you go

 

[Dustin Hoffman]

Here, I believe this shall do it

When I ran this it appears it was still incorrectly driving Family Income Protector and Managed Annuity Program to the wrong location. I highlighted the examples on the BoB tab. I also updated the reference chart to outline what it was supposed to do. I should have done that ahead of time. Sorry about that.

 

[PCosta87]

Darn Unfortunately the order automatically pulls from another internal report and making changes to that can take a while. If you are able to revise the code it would be great but if you can't we can just manually move the data before pasting it in.

Fixed

 

[PCosta87]

When I ran this it appears it was still incorrectly driving Family Income Protector and Managed Annuity Program to the wrong location. I highlighted the examples on the BoB tab. I also updated the reference chart to outline what it was supposed to do. I should have done that ahead of time. Sorry about that.

I'm pretty sure the reference chart is the problem... in fact the "From" and "To" dates were incorrect. You had:


when it should be:

Test the attached file where I've imported the reference chart and made the necessary corrections. Let me know if it is working correctly.

 

[PCosta87]

Hiding the Tabs no info is being Driven to: Perfect! thank you.

Color Coding Each Tab. I've attached an example where I updated the following tabs:

"02 Products and Older" - This one and Misc both work a little different than the rest since there are so many possibilities. On the right there are several columns though outline what's available for each policy scheme type. For example I highlighted all of the LK2 policy numbers the same color green and then I copy and pasted the check boxes over to the left for only the policy schemes that apply. The schemes are all identified in (parenthesis) within each header on the right hand side columns. Anything not identified on the right should remain white as they have all of the funds on the left available to them.

"WRL" & "ML & Dreyfus" - These you don't have to worry about yet as I am not done building all of the options so I will manually color code these for a while.

"No Rider Restrictions" I've got similar columns on the right here but I also have a key for this and the other tabs. Both list out the policy schemes that I want highlighted in each respective color. Example: I have one policy that "contains" PPM so I color coded that to match the yellow in the key and the corresponding hidden column on the right. Example 2: I have several policies that contain either PM8 or PM3, since they share a color I color coded them both to match that color in the key. Example 3: Since I did not have any policies that contain "LST" I hid column "H" and Row "7".

"RIC 1.2 MAY.09" - Similar to No Rider Restrictions, since I did not have and LK3 I hid the corresponding row and column in the key. I color coded a couple policy numbers orange to match the PM8 listed in the key. Lastly. I manually added "Open" to two policies in Column "O" and highlighted those to match the "Open" listed in the key.

Hope that all makes sense

Thank you again for everything.

I think I get it now, although that will very very hard to do (well, at least for me).

As you can see below, you have "STA" (Grey), "RIB" (Yellow(ish)) and "LK2" (Green). These should be fairly simple to match with the Policy Number:


However, "Most Core", for instance, is not because it is divided into:


Only here can you find the criteria to match against the Policy Number. To make matters worse, these are all in a single cell.

I'm afraid I'm unable to help with this particular task

 

[Dustin Hoffman]

Fixed

Perfect This is fixed but the code for this function is not on the last attachment you sent.

 

[Dustin Hoffman]

I'm pretty sure the reference chart is the problem... in fact the "From" and "To" dates were incorrect. You had:
View attachment 42014

when it should be:
View attachment 42015
Test the attached file where I've imported the reference chart and made the necessary corrections. Let me know if it is working correctly.

Thank you. I see where I made the mistake on the reference chart. This is all fantastic.

 

[Dustin Hoffman]

PCosta,

Thank you. This all looks great. No worries on the color coding. The rest of the changes are great. Is it possible to combine a few of these?

• Correction to the Chart correctly driving Managed Annuity Program and Family Income Protector
• Correction to Investment Method Column on the two RIC 1.2 tabs
• Having "Rider Type", "Rider Base" & "Rider Start Date" Columns being filled out for the tabs that are still not pulling them in for? (No Rider Restrictions, ML & Drefus, 02 Products or Older and Misc)

I promise this will be it again for a while You have obviously been a huge help and throughout the process you have been patient with my ignorance. I really appreciate that and the lessons

 

[Dustin Hoffman]

PCosta,

Thank you. This all looks great. No worries on the color coding. The rest of the changes are great. Is it possible to combine a few of these?

• Correction to the Chart correctly driving Managed Annuity Program and Family Income Protector
• Correction to Investment Method Column on the two RIC 1.2 tabs
• Having "Rider Type", "Rider Base" & "Rider Start Date" Columns being filled out for the tabs that are still not pulling them in for? (No Rider Restrictions, ML & Drefus, 02 Products or Older and Misc)

I promise this will be it again for a while You have obviously been a huge help and throughout the process you have been patient with my ignorance. I really appreciate that and the lessons

It appears I was incorrect and the first two bullets were combined on your last example. Thank you. If you are just able to complete my third bullet on the attached document that would satisfy most of what I was looking for.

 

[PCosta87]

Hi,

At the time when I first worked on this code there were only 5 columns in "No Rider Restrictions" and "02 Products or Older" hence why I coded it this way. Upon updating the code I failed to realize that there were more columns needed this time around
It should be ok now...

EDIT: Notice that in BOB some of the samples don't have any data on these 3 columns! Because of that, "Misc", "02 Products or Older" and "ML & Dreyfus" will still not show anything on these columns, although they are being pulled as well.