Working With Filtered Data - Adapting Code From Unfiltered to FIltered Datasets

[Ark68]

I have this code which populates a defined range of cells of a worksheet (ws_gui1 range B6:AM40) with data from a second worksheet in a second workbook (ws_cd1 in wb_rmr). Tghe uniqueness of this code is that it adds the function of a scroll bar if the number of records being copied between the sheets exceeds the static 35 rows of data in the presentation range (B6:AM40).

This works wonderfully when ws_cd1 is just raw unfiltered data. I am wondering how I would need to adapt this code if ws_cd1 was filtered? ASometimes I want the user to be able to just see select records from the entire database in the presentation panel (ie filtered data)

Sub DPOP1()
    'Stop
    Dim page As Double
    With ws_gui1
        .Shapes("dheader_mask").Visible = False
        cnt_rows = Application.WorksheetFunction.Count(ws_cd1.Columns(1))
        drows = cnt_rows + 1
        If cnt_rows > 35 Then 'change this to 35 for go live
            mxds = cnt_rows - (35 - 1) 'cnt_rows - (35 - 1) 35 for go live
            With datascroll
                .Visible = True
                .Value = 0
                .Min = 1
                .Max = mxds
                .SmallChange = 1
                .LargeChange = 35 '35
                .LinkedCell = "Sandbox!$D$1"
                .Display3DShading = True
             End With
            .Range("B6:B40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!A2:$A$" & drows & ",Sandbox!$D$1)"
            .Range("C6:C40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!L2:$L$" & drows & ",Sandbox!$D$1)"
            .Range("D6:D40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!M2:$M$" & drows & ",Sandbox!$D$1)&"""""
            .Range("E6:E40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!D2:$D$" & drows & ",Sandbox!$D$1)"
            .Range("F6:F40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!E2:$E$" & drows & ",Sandbox!$D$1)"
            .Range("G6:G40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!I2:$I$" & drows & ",Sandbox!$D$1)"
            .Range("L6:L40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!J2:$J$" & drows & ",Sandbox!$D$1)"
            .Range("V6:V40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!F2:$F$" & drows & ",Sandbox!$D$1)"
            .Range("AL6:AL40").Formula = "=INDEX('[" & wb_pbef.Name & "]CORE_DATA'!G2:$G$" & drows & ",Sandbox!$D$1)"
        Else
            With datascroll
                .Visible = False
            End With
            DPOP2 page
        End If
        
        With Range("AP4:AT4")
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .Color = RGB(191, 143, 0)
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .Color = RGB(191, 143, 0)
            End With
        End With
        
        CHK_PERMIT
    
    End With
End Sub

 

[Ark68]

Bump

 

[Ark68]

The method used in my OP to move data between sheets works when the rows of source data are contingous. When filtered, they are no longer contingous so I believe this code will copy over all the rows regardless of the filter. I think I need to find an alternative to copying over just the filtered rows. My OP uses a formula applied to the destination columns to accomplish this, but I don't think these, or other, formulas would do that for filtered data.