Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
164 Posts
Discussion Starter · #1 ·
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.
 

·
Registered
Joined
·
164 Posts
Discussion Starter · #3 ·
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.
 

Attachments

·
Registered
Joined
·
2,895 Posts
You can give this code a try:

Code:
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.
 

·
Registered
Joined
·
2,895 Posts
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.
 

·
Registered
Joined
·
164 Posts
Discussion Starter · #8 ·
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.
 

·
Registered
Joined
·
8,546 Posts
Assuming your PT sheet is active:

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

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

End Sub
 
1 - 10 of 10 Posts
Status
Not open for further replies.
Top