# Solved: Excel VBA find last row in pivot table



## mariaa33 (Aug 7, 2008)

I need help with vba code to find the last row in a pivot table that is greater than 49. I want to use this to set a range so I may copy from row 1 to the row found to a new spreadsheet. I have looked online but can't figure it out. Any help will be appreciated.


----------



## computerman29642 (Dec 4, 2007)

Could you post a sample file (replace sensitive data with dummy data)?


----------



## mariaa33 (Aug 7, 2008)

Sample Pivot Table attached.

So what I am trying to do is find the last row that is greater than 49. In this case above I want to find the row starting with PRRNF0990. Once I find that row, I want to copy from row 1 to that row and paste into new worksheet.


----------



## computerman29642 (Dec 4, 2007)

Can you attach the actual file?


----------



## mariaa33 (Aug 7, 2008)

No, it has too much data on it that can't be shared.


----------



## computerman29642 (Dec 4, 2007)

You can give this code a try:


```
Sub Sample()

    Dim colStart As Integer, rowStart As Integer
    Dim rowCounter As Long, lastRow As Long
    Dim wskSource As Worksheet, wskDest As Worksheet

    Set wskSource = Sheets(1)
    Set wskDest = Sheets(2)

    wskSource.Activate
    wskSource.Range("D2").Select

    colStart = ActiveCell.Column
    rowStart = ActiveCell.Row

    lastRow = ActiveCell.SpecialCells(xlLastCell).Row

    For rowCounter = lastRow To 1 Step -1
        If wskSource.Cells(rowCounter, colStart) > 49 Then
            Range(rowStart & ":" & rowCounter).Copy
            wskDest.Activate
            wskDest.Range("A1").Select
            wskDest.Paste
            Application.CutCopyMode = False
            Exit Sub
        End If
    Next rowCounter

End Sub
```
Someone else maybe able to come up with a better way, but this should work.


----------



## computerman29642 (Dec 4, 2007)

I should mention that I did not test this with a pivot table. I do not have a workbook that contains a pivot table. I am not sure if that makes a difference or not.


----------



## mariaa33 (Aug 7, 2008)

That worked great. Is there anyway it will work without exiting the SUB when the pasting is finished? There are some other things I want to do with the pasted data in vba. I did try commenting out the exit sub just to see and it does weird stuff with that data.


----------



## bomb #21 (Jul 1, 2005)

Assuming your PT sheet is active:

Sub Sample()
lastRow = Range("D" & Rows.Count).End(xlUp).Row
lastRowGT49 = 1
For Each Cell In Range("D2" & lastRow)
If Cell.Value > 49 Then
lastRowGT49 = Cell.Row
End If
Next Cell

If lastRowGT49 <> 1 Then
Range("D1" & lastRowGT49).Copy Sheets("Sheet2").Range("A1")
End If

End Sub


----------



## mariaa33 (Aug 7, 2008)

I used a variation of the first code and was able to use "Exit For" instead of "Exit Sub". Thank you for your help!!


----------

