• 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.

Macro to copy paste filtered table

NCase

Member
Hello,

Question 1:
I have a table which filtered using excel slicers. The table contains helper columns which aid in the filtering process but I do not want to copy and paste to a new sheet.
My macro copies the entire table including the undesired helper columns.

>>> use code - tags <<<

Code:
Sub CopyFilteredInstrumentData()
Dim rng As Range
Dim WS As Worksheet
For Each Row In Range("Table1[#All]").Rows
    If Row.EntireRow.Hidden = False Then
        If rng Is Nothing Then Set rng = Row
        Set rng = Union(Row, rng)
    End If
Next Row
Set WS = ThisWorkbook.Worksheets("Master")
rng.Copy Destination:=WS.Range("D7")
End Sub

How do I edit my macro to only copy Column A:L of the table in the RawData sheet and not my helper M:Q columns.

Question 2
Instead of a button to click to execute my macro. How can I execute the macro automatically after I make my 2nd selection in the excel slicers for the table. Please see attached excel file for what I am trying to achieve.

Best,

Francis
 

Attachments

  • 2019-01-05_2020 Data Org.xlsm
    176.9 KB · Views: 4
Last edited by a moderator:
Answer to question 1. Works beautifully so far.

The reason I ask for question 2 as I will be automating the entire process based on selections. So extra clicking becomes a error prone pain.

Best,

Francis
 
2. In the attached the macro incorporates suggested changes in my previous message along with another macro to update the Master sheet only on changes of the second slicer (on either sheet). This may not work after first opening the file or directly after resetting the VBA project (the data will get updated on a change of any slicer). Once over that hurdle the data should only update after changes to the second slicer, even if you change the first slicer. Of course, you can still click your button whenever you like.
 

Attachments

  • Chandoo433602019-01-05_2020 Data Org.xlsm
    248.7 KB · Views: 13
Back
Top