• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help automatically moving Source Data from one tab to others

PCosta87 said:
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.
Click to expand...


Thank you. This works great!

The only thing I failed to follow up on is about the VLOOKUP being added to the "BoB" tab. Currently we paste source data into the "BoB" tab that includes codes in the Rider Type column. We then copy those codes and paste them into column A of the "Rider Translator" tab. Then in column B of that same tab the VLOOKUP produces the actual name of the rider referencing the table on the "Rider Codes" tab. We can then copy those names and paste as values back into the Rider Type Column within the BoB tab.

I have not been able to figure out how to get that VLOOKUP to generate an actual name (Rather Than Formula) when we paste all the source data(All 12 columns at once) into the "BoB" tab. Hope that makes sense. Obviously the Macro uses that column so having a formula there won't help.
 
Hi,

In fact, the code uses the value of the cell (what is displayed) and not the actual formula, which means you can have a formula there and it will still work.
You don't need "actual names".

Try it out :)
Let me know if you come across any issue.
 
PCosta87 said:
Hi,

In fact, the code uses the value of the cell (what is displayed) and not the actual formula, which means you can have a formula there and it will still work.
You don't need "actual names".

Try it out :)
Let me know if you come across any issue.
Click to expand...

Would I need to add a column since I currently paste it into one and then it produces the results next to it?
 
Dustin Hoffman said:
Would I need to add a column since I currently paste it into one and then it produces the results next to it?
Click to expand...
I suggest you use a column to the right of the BOB table if that's ok... it is just because I would have to revise the code again if you added a column.
If that is not an option, adding one column before column A would make updating the code easier.
 
PCosta87 said:
I suggest you use a column to the right of the BOB table if that's ok... it is just because I would have to revise the code again if you added a column.
If that is not an option, adding one column before column A would make updating the code easier.
Click to expand...


PCosta,

Thank you. You have been incredible. Is there someway we can send you a gift card?
 
PCosta,

Quick easy follow up question; now when I run the Macro it correctly hides the unused tabs but instead of leaving me on the BoB tab it takes me to the last tab that information is being driven to. Any way to hide and leave me on the bob Tab?
 

Attachments

  • Book of Available Funds 2.0 June 1st Changes.xlsm
    495.2 KB · Views: 1
Dustin Hoffman said:
PCosta,

Quick easy follow up question; now when I run the Macro it correctly hides the unused tabs but instead of leaving me on the BoB tab it takes me to the last tab that information is being driven to. Any way to hide and leave me on the bob Tab?
Click to expand...
Hi,

Sure, we simply need to activate that "BoB" at the end with:
Code: 
Sheets("BoB").Activate

Attached
 

Attachments

  • Book of Available Funds 2.0 June 1st Changes.xlsm
    495.4 KB · Views: 3
Thank you. I will give it a test here in a few but I'm sure it works. Also thank you for showing me what you did. I would love to learn this myself.
 
Dustin Hoffman said:
Thank you. I will give it a test here in a few but I'm sure it works. Also thank you for showing me what you did. I would love to learn this myself.
Click to expand...
You are welcome :)
Chandoo is full of info on VBA if you wish to learn more. You can even sign up for classes here if you'd like.
This site also has several quick lessons that I found useful for people starting with VBA.
 
Hello Again PCosta,

I noticed one thing that does not appear to be working correctly. On the rows of data being moved over to the RIC 1.2 (1) and RIC 1.2 (2) tabs, the "Investment Method" column is not being moved over as it was before. Can you take a look at this by chance?

Also I believe the Managed Annuity Program and the Family income Protector Might be using the Rider Start Date rather than the "Effective Date" to drive from either "02 Products or Older" and "No Rider Restrictions" tabs
 

Attachments

  • Book of Available Funds 2.0 6.6.17 Sample .xlsm
    500.4 KB · Views: 7
Last edited:
Hi,

I'm quite sure that has to do with the changes you made to the reference chart.
As you know, there were specific instructions for "RIC 1.2 MAY.09" and for "RIC 1.2 DEC.11"... neither of these are present on the Reference chart now (they seem to have been replaced with "RIC 1.2 (1)" and "RIC 1.2 (2)")

As this was never mentioned, the code isn't ready to handle it, which is why it is not working properly after the changes.
I believe I've fixed the "Managed Annuity Program" and "Family income Protector" bug so they should be using the effective date now.

As for the "RIC", you will have to replace "RIC 1.2 MAY.09" and "RIC 1.2 DEC.11" in the code with the appropriate tab name (matching the reference chart) as I can't know which one of these - "RIC 1.2 (1)" and "RIC 1.2 (2)" - to use.

Code: 
Sub copy()

    Show
    NewSteps

    Dim c, c1 As Range
    Dim lrow, lrowdest, lrowref, col, i As Integer

    lrowref = Sheets("Reference Chart").Cells(Rows.Count, "B").End(xlUp).Row
    lrow = Sheets("BoB").Cells(Rows.Count, "A").End(xlUp).Row

    i = 2
    Do While i <> 0
        For Each c In Sheets("Reference Chart").Range("B4:B" & lrowref)
            For Each c1 In Sheets("BoB").Range("F2:F" & lrow)
                If (c.Offset(, 2) = "RIC 1.2 MAY.09" Or c.Offset(, 2) = "RIC 1.2 DEC.11") And InStr(c1, c) > 0 And c1.Offset(, i) >= c.Offset(, 3) And c1.Offset(, i) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                    c1.Offset(, 4).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col + 8)
                ElseIf (c.Offset(, 2) = "No Rider Restrictions" Or c.Offset(, 2) = "02 Products or Older") And c <> "Blank" And c <> "Managed Annuity Program" And c <> "Family Income Protector" And InStr(c1, c) > 0 And c1.Offset(, i) >= c.Offset(, 3) And c1.Offset(, i) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                ElseIf (c.Offset(, 2) = "No Rider Restrictions" Or c.Offset(, 2) = "02 Products or Older") And (c = "Blank" Or c = "Managed Annuity Program" Or c = "Family Income Protector") And c1 = "" And c1.Offset(, -3) >= c.Offset(, 3) And c1.Offset(, -3) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                ElseIf InStr(c1, c) > 0 And c1.Offset(, i) >= c.Offset(, 3) And c1.Offset(, i) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                End If
            Next c1
        Next c
        If i = -3 Then
            i = 0
        Else
            i = -3
        End If
    Loop
   
    Hide
    Sheets("BoB").Activate
                 
End Sub
 
OK I Updated the code with RIC 1.2 and that seemed to work. Thank you. It appears the Managed Annuity Program and Family Income Protector is still not working and I'm wondering if it has something to do with the different variations of the names.

Is the code looking for the exact name or just names that start with "Managed Annuity Program or Family Income Protector"?

Family Income Protector
Family Income Protector (WA)
Family Income Protector (NJ)
Managed Annuity Program
Managed Annuity Program Upgrade
Managed Annuity Program II
Managed Annuity Program II Upgrade
 
I am trying something

I'm trying to update the code to mimic what you did with the "Misc" tab and drive all the policy schemes on the "02 Products and Older" using the policy number column.
 
Last edited:
I tried to add a step in the code within the "New Steps" Code to use policy scheme in the policy number column to drive info to 02 Products and Older. Not only did it not work but it stated that command would stop the debugger.

ub NewSteps()

Dim c As Range
Dim lrow As Integer

With Sheets("BoB")
lrow = .Cells(Rows.Count, 1).End(xlUp).Row

For Each c In .Range("A6:A" & lrow)
If c.Offset(, 12) = "" And Left(c, 1) <> "" And (IsNumeric(Left(c, 1)) = False Or Left(c, 1) = 7) Then
.Range(c, c.Offset(, 7)).copy Sheets("ML & Dreyfus").Cells(Rows.Count, 1).End(xlUp).Offset(1)
c.Offset(, 12) = "ML & Dreyfus"
End If
If c.Offset(, 12) = "" And (InStr(c, "TAT") Or InStr(c, "PSA") Or InStr(c, "PS2") Or InStr(c, "PS3") Or InStr(c, "RB2") Or InStr(c, "RB3")) Then
.Range(c, c.Offset(, 7)).copy Sheets("Misc").Cells(Rows.Count, "D").End(xlUp).Offset(1)
c.Offset(, 12) = "Misc"
End If c.Offset(, 12) = "" And (InStr(c, "LK2") Or InStr(c, "EDV") Or InStr(c, "E97") Or InStr(c, "SBS") Or InStr(c, "P97") Or InStr(c, "P98")Or InStr(c, "FRM")Or InStr(c, "EVA")Or InStr(c, "EV1")Or InStr(c, "EV2")Or InStr(c, "E98")Or InStr(c, "LMK")Or InStr(c, "E2K")Or InStr(c, "LK1")Or InStr(c, "M98")Or InStr(c, "M2K")Or InStr(c, "MLL")Or InStr(c, "ML1")Or InStr(c, "ML2")Or InStr(c, "AVA")Or InStr(c, "RIB")Or InStr(c, "STA")) Then
.Range(c, c.Offset(, 7)).copy Sheets("Misc").Cells(Rows.Count, "D").End(xlUp).Offset(1)
c.Offset(, 12) = "02 Products or Older"
End If
If c.Offset(, 12) = "" And IsNumeric(Mid(c, 3, 1)) = False And Mid(c, 3, 1) <> "" Then
.Range(c, c.Offset(, 7)).copy Sheets("WRL").Cells(Rows.Count, 1).End(xlUp).Offset(1)
c.Offset(, 12) = "WRL"
End If
Next c
End With

End Sub
 
PCosta,

Sorry for several messages. It appears I figured out how to add to the code to in order to drive certain policy schemes over to the "02 Products and Older Tab" You'll notice I just mimicked what you did for the "Misc" Tab.

Here is what I was not able to correctly do

  1. I also tried adding code that stated "anything that starts with a 5" should also route to the "02 Products or Older" tab. You'll notice the example on line 71 on the "BoB" tab routed correctly(probably because of the original code) but examples on line 46 & 47 did not. Any Ideas? Note: you'll also notice examples like the one on line 54 contains both PS3 and starts with a 5. That was just a test to see if it would work I will never have that combo.
  2. Lastly, it still appears anything that contains "Managed Annuity Program" or Family Income Protector" is still using Rider Start Date rather than Effective Date. Examples on Rows 38 & 39 Any ideas?
 

Attachments

  • Book of Available Funds 2.0 6.7.17 Test.xlsm
    514.6 KB · Views: 3
Dustin Hoffman said:
OK I Updated the code with RIC 1.2 and that seemed to work. Thank you. It appears the Managed Annuity Program and Family Income Protector is still not working and I'm wondering if it has something to do with the different variations of the names.

Is the code looking for the exact name or just names that start with "Managed Annuity Program or Family Income Protector"?

Family Income Protector
Family Income Protector (WA)
Family Income Protector (NJ)
Managed Annuity Program
Managed Annuity Program Upgrade
Managed Annuity Program II
Managed Annuity Program II Upgrade
Click to expand...
Hi,

The code works with full match in this 2 cases, which is why it won't work properly with partial matches.
At the time that was my understanding of the request.
I fixed it so it now looks for a partial match in the reference chart.
Also, I figured where the effective date problem was... it should also be fixed.
Try it out and let me know for any problems:
Code: 
Sub copy()

    Show
    NewSteps

    Dim c, c1 As Range
    Dim lrow, lrowdest, lrowref, col, i As Integer

    lrowref = Sheets("Reference Chart").Cells(Rows.Count, "B").End(xlUp).Row
    lrow = Sheets("BoB").Cells(Rows.Count, "A").End(xlUp).Row

    i = 2
    Do While i <> 0
        For Each c In Sheets("Reference Chart").Range("B4:B" & lrowref)
            For Each c1 In Sheets("BoB").Range("F2:F" & lrow)
                If (c.Offset(, 2) = "RIC 1.2 MAY.09" Or c.Offset(, 2) = "RIC 1.2 DEC.11") And InStr(c1, c) > 0 And c1.Offset(, i) >= c.Offset(, 3) And c1.Offset(, i) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                    c1.Offset(, 4).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col + 8)
                ElseIf (c.Offset(, 2) = "No Rider Restrictions" Or c.Offset(, 2) = "02 Products or Older") And c <> "Blank" And InStr(c, "Managed Annuity Program") = 0 And InStr(c, "Family Income Protector") = 0 And InStr(c1, c) > 0 And c1.Offset(, i) >= c.Offset(, 3) And c1.Offset(, i) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                ElseIf (c.Offset(, 2) = "No Rider Restrictions" Or c.Offset(, 2) = "02 Products or Older") And (InStr(c, "Managed Annuity Program") = 1 Or InStr(c, "Family Income Protector") = 1) And c1.Offset(, -3) >= c.Offset(, 3) And c1.Offset(, -3) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                ElseIf (c.Offset(, 2) = "No Rider Restrictions" Or c.Offset(, 2) = "02 Products or Older") And c = "Blank" And c1 = "" And c1.Offset(, -3) >= c.Offset(, 3) And c1.Offset(, -3) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                ElseIf InStr(c1, c) > 0 And c1.Offset(, i) >= c.Offset(, 3) And c1.Offset(, i) <= c.Offset(, 4) And IsEmpty(c1.Offset(, 7)) = True Then
                    c1.Offset(, 7) = c.Offset(, 2)
                    col = Sheets(c1.Offset(, 7).Value).Cells.Find(what:="Policy Number").Column
                    lrowdest = Sheets(c1.Offset(, 7).Value).Cells(Rows.Count, col).End(xlUp).Offset(1).Row
                    Range(c1.Offset(, -5), c1.Offset(, 2)).copy Sheets(c1.Offset(, 7).Value).Cells(lrowdest, col)
                End If
            Next c1
        Next c
        If i = -3 Then
            i = 0
        Else
            i = -3
        End If
    Loop

    Hide
    Sheets("BoB").Activate
              
End Sub
 
PCosta,
It appears that Effective Date issue still exists. See lines 46,47 and 49 on the BoB tab of the attached document.

It appears I figured out how to add to the code to in order to drive certain policy schemes over to the "02 Products and Older Tab" You'll notice I just mimicked what you did for the "Misc" Tab.

Here is what I was not able to correctly do
  1. I also tried adding code that stated "anything that starts with a 5" should also route to the "02 Products or Older" tab. You'll notice the example on line 71 on the "BoB" tab routed correctly(probably because of the original code) but examples on line 46 & 47 should have routed prior to it looking at the Rider Type Column. Any Ideas?
 

Attachments

  • Book of Available Funds 2.0 6.7.17 Test.xlsm
    517.1 KB · Views: 4
Hi,

I'm sorry but I can't really go through the code and properly debug it at the moment.
I will try my best to look at it with a bit more time in the weekend. If I can't, Monday I'll see if I can debug the problem and fix it.

Sorry again... hope this isn't very urgent :(
 
PCosta87 said:
Hi,

I'm sorry but I can't really go through the code and properly debug it at the moment.
I will try my best to look at it with a bit more time in the weekend. If I can't, Monday I'll see if I can debug the problem and fix it.

Sorry again... hope this isn't very urgent :(
Click to expand...


No Worries at all. Whenever you get a chance. Thank you
 
Dustin Hoffman said:
PCosta,
It appears that Effective Date issue still exists.
Click to expand...

Hi,

I can't seem to be able to replicate the issue here... notice below where I changed the date to 2004 for 2 of the MAP and it routed correctly using my last code (at least I think it did):
1.gif

Can you please confirm that the above is how it is supposed to be.
Thanks
 
PCosta87 said:
Hi,

I can't seem to be able to replicate the issue here... notice below where I changed the date to 2004 for 2 of the MAP and it routed correctly using my last code (at least I think it did):
View attachment 42457

Can you please confirm that the above is how it is supposed to be.
Thanks
Click to expand...

PCosta,

The attached document has only a few examples. Rows 6, 7 & 9 (in Red) appear to be incorrectly being labeled as (No Rider Restrictions) when the effective date should be driving it to the "02 Products or Older" tab. The other rows (in Green) are correctly routing I believe due to the changes I made within the "New Steps" macro.

Hopefully that makes sense. Thanks as always for taking a look.
 

Attachments

  • Sample 5.12.17 Book of Available Funds 2.0.xlsm
    499.3 KB · Views: 5
Last edited:
Hi,

Please test this new version and see if it is working as intended.
It is still using my last code (as well as the "new steps" before your changes) with a slight tweak.

Let me know for any problems.
Thanks
 

Attachments

  • Sample 5.12.17 Book of Available Funds 2.0.xlsm
    498.5 KB · Views: 12
PCosta87 said:
Hi,

Please test this new version and see if it is working as intended.
It is still using my last code (as well as the "new steps" before your changes) with a slight tweak.

Let me know for any problems.
Thanks
Click to expand...

It appears the it's correctly driving the MAP policies! Although it appears to be now Driving Income Select to "No Rider Restrictions" rather than to the "Income Select" Tab. I wasn't able to test all the other Rider Types but this one was working before.
 
Hi,

It appears I deleted (by mistake) one of the conditions in one of the IF statements when I made the last changes.
I believe this will fix it but as always test it out and let me know.
 

Attachments

  • Sample 5.12.17 Book of Available Funds 2.0.xlsm
    498.7 KB · Views: 4
You must log in or register to reply here.
Back
Top