Hi F
I think the following (might) get you the LastRow on each sheet that you want.
Not sure about the copy and paste part.
Sub LastDataRow_BeforeSubTotal()
Dim oSheet As Excel.Worksheet
Dim SubTotalRow As Long
Dim SubTotalRow_MinusOne As Long
'-------------
' Each Worksheet in Workbook (excludes non-worksheets)
'-------------
For Each oSheet In Worksheets
'-------------
' Name of Workshet
'-------------
'MsgBox oSheet.Name
'-------------
' Two Cases - get WS LastRow or ignore WS
'-------------
Select Case UCase(oSheet.Name)
Case "WS_SUMMARY", "WS_PIVOTTABLE", "WS_IGNORE_ME"
'----------------------------- Do Nothing
Case Else
'-------------------
' **Assumes** data ALWAYS in column A
'-------------------
With Sheets(oSheet.Name)
SubTotalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
SubTotalRow_MinusOne = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
'
' Your code here
'
End With
End Select
Next
Exit Sub
End Sub
hth
regards
John
Bookmarks