I have a data table on one sheet, and a pivot on the next.

When I click something on the pivot table I want each recrod in the data table to be examined for a matching number. I have this portion figured out, what I can't figure out is how to find the last row of the data table for my for...next loop. Anything I find will only find the last row of the active sheet.

Any help is appreciated

Here is my function for finding the last row:

Function LastRow(AcctNumber As Integer) As Integer
'--------------------------------------------
' Procedure : LastRow
' Author : kthompson
' Date : 12/21/2010
'
'
'--------------------------------------------
On Error GoTo LastRow_Error
Dim LRow As Integer

'This is where I don't know how to make it look at the data sheet
'instead of the pivot table sheet. The with part doesn't work
With Worksheets("2010 DATA")

LRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End With

Dim i As Integer
For i = 2 To LRow - 1
If Sheets("2010 DATA").Range("D" & i).Value = AcctNumber Then
Sheets("2010 DATA").Range("K" & i) = Sheets("2010 TABLE").Range("D2")
End If
Next i

LastRow_Exit:
Exit Function
LastRow_Error:
MsgBox (Err.Number & ", " & Err.Description & ", Record Number: " & i)
Resume LastRow_Exit

End Function

WooHoo! I figured it out... My logic got mixed up... Silly With block doesn't need to be there.... Just this:
Function LastRow(AcctNumber As Integer) As Integer
'--------------------------------------------
' Procedure : LastRow
' Author : kthompson
' Date : 12/21/2010
'
'
'--------------------------------------------
On Error GoTo LastRow_Error
Dim LRow As Integer

LRow = Worksheets("2010 DATA").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim i As Integer
For i = 2 To LRow - 1
If Sheets("2010 DATA").Range("D" & i).Value = AcctNumber Then
Sheets("2010 DATA").Range("K" & i) = Sheets("2010 TABLE").Range("D2")
End If
Next i

LastRow_Exit:
Exit Function
LastRow_Error:
MsgBox (Err.Number & ", " & Err.Description & ", Record Number: " & i)
Resume LastRow_Exit

End Function

I could have sworn that I had tried something like it already, but then in thinking about how to do it best, I got mixed up :p

FWIW: There's also a UsedRange which is very useful in telling you the boundary of where there are content and thus can be used to identify the last row/column that's actually used.

Or you could do it like this:


Public Function RHLastRow(ws As Worksheet) As Long

RHLastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

End Function


Seasons greetings