I have never really used VBA before, so let me say thanks in advance for anyone that can help me out!! Hopefully this is very basic and the response will be quick and easy to implement.

Here is my issue --- I have a workbook with many tabs, all of which are pulling data from a master pivot table at the back of the workbook using an Index/Match function. Once the proper data has been properly pulled into each tab, I want to use a macro to copy and paste the values; however, the tabs have varying numbers of rows and I want to be able to have the macro dynamically find the end of the row in each tab. Another issue is that the final row is actually a subtotal row, and I do NOT want to paste values in that row; I want that row to remain dynamic. So basically, I want the macro to find the last row and then go up 1 row before performing the copy and paste values function. I've pasted my code below. You'll note that for the tab in which I recorded the macro, the last row before the subtotals is '176', which is not the case for all tabs.

If anyone can specifically tell me the code I need to replace and the corresponding code I need to replace it with, I would greatly appreciate it.

Please let me know if you need additional information to be able to help me out.



Sub PasteValues()
'
' PasteValues Macro
'

'
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
ActiveWindow.SmallScroll Down:=-7536
Range("I9").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("I9:BQ9").Select
Range(Selection, Selection.End(xlDown)).Select
Range("I9:BQ176").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("D9").Select
End Sub