Need dynamic VBA code to find last row of data (one row before subtotals)

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

Hello,

I'm not a pro either but I faced the same problem. I solved it like this, what might work as long as the column you use to find tha last row has no blank cells:
Selection.Copy
Range("A1").select 'select cell A1
selection.end(xlDown).select 'select the last (filled) cell in the column, which should be the subtotal row

ActiveCell.Rows("1:1").EntireRow.Select 'Select the entire bottum row
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'insert a new row
With Selection
.Locked = False

You should then be able to define your paste code

Good luck

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.

Please Login or Register  to view this content.

hth
regards
John

Sorry, but I'm not following either of thoe previous posts (not because they didn't or won't work, but because I basically need step by step instructions). Like I said, I'm truly a beginner when it comes to VBA and macros, so if you can tell me exactly what I need to paste into the code, where I need to paste it, and any code I need to delete, I would greatly appreciate it.

Also, to clarify, let's just assume that I already know ALL tabs (except my master pivot tab that is supplying data to the tabs in reference) have Match/Index data in columns B through Q, but that each tab has a different number of rows of data (tab 1 goes from row 1 to 176, tab 2 goes from row 1 to row 55, tab 3 goes from row 1 to row 312, etc). What do I need to change in the code I submitted to get it to go to the row before the subtotal row (I also know there are no blank rows) in each respective tab so that it dynamically highlights the array (excluding the subtotal row) and I can then apply the Copy > Paste Values function?

Additionally, because I have other arrays within each worksheet in addition to the column B-Q array (i.e. W through AB and AG through AX) that also have data in the same rows, so I would like to be able to use the code for those arrays as well (simply adjusting the code for the column labels).

Again - any help is greatly appreciated!!!