none
Values to move with rows in a filtered table: perhaps VBA? RRS feed

  • Question

  • Hi all excel brains,

    I need help move cell values with the rest of data in a filtered table! I must say though this is quite hard to explain in writing. I'll give my best lol. *I have a feeling VBA might be what i need. 

    I have two sheets. One source, and the other destination.  shown in the picture below, my source table has all information (i.e. no filter) and my destination table has a filter on “BBC Knowledge” for Channel. When someone adds a new row of data (e.g. for “BBC Knowledge”) to the source sheet, it should automatically be added to and displayed in the destination sheet without manual copying and pasting. 

    Well, I have managed to achieve this using Data Connections and PowerQuery/Pivot.

    The problem is that I also want to be able to add extra information to the destination sheet, e.g. column <MPX> in the red box in the screen shot below, which is not originally in the source sheet. And these values are added to the destination sheet and unique to that data (specifically to the Media ID) and must stay with it.

    However, when I add a new row to the source sheet, those values go out of order! 

    Screen shot 1. Source table, and Destination table with filter on “BBC Knowledge”

    Screen shot 2. A new row is inserted in the source sheet as below (row 64 with a title “test”) -> refresh the destination table -> the new data “test” is added -> but the MPX values go out of whack (as in the blue box)! For instance, "24 Hours in A&E S5E12" had a value of "10" in the MPX column as in the picture above. But after a new row is added to the source table, it ended up being "11" for MPX in the destination table below. 

    My guess is that as the new row is added, it pushes all the MPX values (1,2,3,4,5…) down by one row, rather than them sticking with the data. And by pushing them down, some of the values end up with channels other than “BBC Knowledge” and therefore those values won’t appear in the destination sheet that has a filter on “BBC Knowledge”.

    My question is, how do i make a cell value stay with the rest of data in the row in a filtered table when a new is inserted/deleted? 

    Perhaps VBA that could make values in one column stay with the values in another column. In my case, the value in the <MPX> column (K) sticks with its corresponding value in the <Media ID> column (J), so when a Media ID moves up or down the row, the <MPX> value moves with it. 

    Hope i'm making sense. And please please help! 

    Tuesday, March 21, 2017 8:12 AM

Answers

  • Hi jay.nz,

    you can try to use code like below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim wbsource, wbtarget As Workbook
    Dim shtsource, shttarget As Worksheet
    Dim LastRow, LastRow2 As Long
    Dim str, str1 As Range
    Set wbsource = ActiveWorkbook
    Set wbtarget = Workbooks.Open("C:\Users\v-padee\Desktop\test file destination.xlsx")
    Set shtsource = ThisWorkbook.Worksheets("Affiliate  catch-up")
    Set shttarget = Workbooks(wbtarget).Worksheets("Sheet1")
    LastRow = shtsource.Cells(shtsource.Rows.Count, "A").End(xlUp).Row
    LastRow2 = shttarget.Cells(shttarget.Rows.Count, "A").End(xlUp).Row
    str = "A" & LastRow & ":H" & LastRow
    str1 = "A" & LastRow2 & ":H" & LastRow2
    Worksheets("shtsource").Range(str).Copy _
        Destination:=Workbooks(wbtarget).Sheets("Sheet1").Range(str1)
    wbsource.Save
    wbtarget.Save
    End Sub
    
    

    first try to test this code on a sample workbooks.

    remove all your queries , when new record will be enter it will copy the record to other workbook and save it.

    then you can add the value for MPX and again save it.

    modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jay.nz Sunday, March 26, 2017 6:06 AM
    Thursday, March 23, 2017 6:51 AM
    Moderator

All replies

  • Hi jay.nz,

    first of all I need to understand , how you assign the value of MPX column in the destination sheet ?

    when you enter a new record in destination table, values of last MPX cell get changed or disappear?

    am I understand correct?

    I can assume that it is incrementing by 1 with a new record.

    try to describe the steps to reproduce the issue and if possible try to post the file.

    so that we can try to check how you add a new record to destination sheet and how it calculate and assign the value of MPX column.

    we only support for VBA code so we are not familiar with power query / pivot. (there is a specific forum available for this.)

    so may be it is possible that we will try to replace the power query / pivot part with VBA code.  

    you can try to use Sheet_change event. when sheet get changed then it will execute a code.

    you need to find a way to check if it is new record then you can copy that record to destination sheet. at the same time you can generate the value for MPX column and at the end you can save everything.

    so whenever you add a new record , nothing will get changed in older records.

    This is just a logic, if you try to provide your workbook then we can try to provide you a sample demo code.

    further you can modify that code according to your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 5:43 AM
    Moderator
  • Hi Deepak,

    My answers as below:

    1. how you assign the value of MPX column in the destination sheet ?

    A. We enter those values manually upon checking a record against another system. They are codes, e.g. 2003, 4001, 6002, etc. 

    2. when you enter a new record in destination table, values of last MPX cell get changed or disappear?

    A. We don't enter a new record into the destination table. We only enter a value to the MPX column. Records are entered into the source table only. But of course, the destination table will show records that meet certain criteria (e.g. only the records that are for "BBC Knowledge", and so on). 

    3. it is incrementing by 1 with a new record.

    A. Not really, i entered the numbers 1 to 15 as an example, but as i mentioned above in answer to 1, MPX values are codes like 2003, 4001, 6002, and so on. 

    Basically there should be one source sheet where i continuously add new raw data, and multiple destination sheets (e.g. one that only shows BBC records out of the source, another that shows UKTV records) which my team members add extra information to. 

    Sample files are here. Not sure if they retain the connection...

    https://drive.google.com/open?id=0B1y9snTlTlZeeVdYSlJhdEo0Snc
    https://drive.google.com/open?id=0B1y9snTlTlZeLUs2cDhwWkN4Z0E

    Here's what I did.

    1. Open the destination sheet and take notice of how the MPX values are entered
    2. Add a new record to the source (enter "BBC Knowledge" for channel)
    2. Save
    3. Refresh the destination sheet (go to Data -> Refresh all)
    4. You will see the MPX value have gone out of whack

    Hope this helps...

    Thanks!

    Jay

    • Edited by jay.nz Wednesday, March 22, 2017 6:56 AM
    Wednesday, March 22, 2017 6:18 AM
  • Hi jay.nz,

    you can try to use code like below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim wbsource, wbtarget As Workbook
    Dim shtsource, shttarget As Worksheet
    Dim LastRow, LastRow2 As Long
    Dim str, str1 As Range
    Set wbsource = ActiveWorkbook
    Set wbtarget = Workbooks.Open("C:\Users\v-padee\Desktop\test file destination.xlsx")
    Set shtsource = ThisWorkbook.Worksheets("Affiliate  catch-up")
    Set shttarget = Workbooks(wbtarget).Worksheets("Sheet1")
    LastRow = shtsource.Cells(shtsource.Rows.Count, "A").End(xlUp).Row
    LastRow2 = shttarget.Cells(shttarget.Rows.Count, "A").End(xlUp).Row
    str = "A" & LastRow & ":H" & LastRow
    str1 = "A" & LastRow2 & ":H" & LastRow2
    Worksheets("shtsource").Range(str).Copy _
        Destination:=Workbooks(wbtarget).Sheets("Sheet1").Range(str1)
    wbsource.Save
    wbtarget.Save
    End Sub
    
    

    first try to test this code on a sample workbooks.

    remove all your queries , when new record will be enter it will copy the record to other workbook and save it.

    then you can add the value for MPX and again save it.

    modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jay.nz Sunday, March 26, 2017 6:06 AM
    Thursday, March 23, 2017 6:51 AM
    Moderator
  • Thanks Deepak!

    Actually i'm getting this error now. I dont know what i've done lol.

    How do i fix it? 

    


    • Edited by jay.nz Thursday, March 30, 2017 11:26 PM
    Sunday, March 26, 2017 6:07 AM