0

I am trying to copy a table with a dynamic range, and I would like to reference the bottom cell of the table by using the INDIRECT function. Here is my current code:

Sub CopyandPasteData()
    Sheets("Pivot Tables").Select
    Range(Range("H10"), Range("M10").End(xlDown)).Copy
    Sheets("Final Table").Select
    Range("A1").Select
    Range("A1").PasteSpecial xlPasteValues

End Sub

The reason why the above formula doesn't work is because the table sometimes has blank cells in it, so using "End" will only run down to the first blank cell in the table.

So... I have a formula setup to give the bottom row of the table in a cell on the Pivot Table sheet, this formula is in Cell I8. It just returns a number, ie. "61". What I am essentially trying to do is say: Range("H10"), Range("M"&"INDIRECT"$I$8").Copy Instead of using Range("H10"), Range("M10").End(xlDown)).Copy, however this syntax is obviously not correct.

How can I go about using the INDIRECT function in this formula to find the bottom of the table?

user212443
  • 1
  • The linked question is the canonical example of how to find the last cell. No need for `INDIRECT` or a formula setup. – BigBen Feb 07 '23 at 17:45
  • @BigBen This is helpful, but i am not sure how to incorporate this method into my currently existing code. I am really new to VBA... Any suggestions? Thanks! – user212443 Feb 07 '23 at 18:23
  • `With ThisWorkbook.Worksheets("Pivot Tables")`, `Dim lastRow As Long`, `lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row`, `End With`? as a start? – BigBen Feb 07 '23 at 19:12

0 Answers0