Select data from pivot table - number or rows with data varies

    I am trying to get a macro to copy a column of data from a pivot table and copy just the cells with data to another tab. The problem I am running into is the number of rows in the pivot table changes. I tried


    Sheets("Summary").Select

    Range("S6").Select

    If ActiveCell.Value = "" Then

    Else:

    LastRowColumns = Cells(Rows.Count, 1).End(xlUp).Row

    Range("S6:s" & LastRowColumns).Select

    End If


    but when there is only one row of data in the pivot table it selects the entire column down to the bottom of the page (row 1048576).


    Can anyone tell me how to select a range starting from a cell (S6), select the range of cells below it with data if the number of cells below it ranges from one (cell S6 only) or several?

    The pivot table has a Tablerange1 property which is the body of the pivot table. You can use that to figure out which rows to copy. But your current code should never select all the rows in the sheet.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login