VBA how to copy to second last cell in table?

Question

I have a pivot table with data, I need to copy and past all of the data except for the last row, which I need to delete as it is a sum of totals. The amount of data entries vary by week so I cannot just put the set numbers in. Currently my code looks like this;

Worksheets("Mobile Summary").Range("A4:F482").Copy 
Worksheets("Sheet1").Range("A1:F479")

However I need to copy and paste more data from a different pivot table directly under this. Therefore I couldn't just set the rows to 3000 for example. I know there is a code to find the last row, although I'm coming up short as to how to go about solving this problem.

Please see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) to find the last row and then simply create your range as `Worksheets("Mobile Summary").Range("A4:F" & LastRow -1).Copy` — Siddharth Rout, Jan 10 '19 at 09:44

score 0 · Accepted Answer · answered Jan 10 '19 at 09:49

To find a pivot table by its name and remove the last row of the table use the Range.Resize property. This is useful if there is more than one pivot table on a worksheet.

Option Explicit

Sub CopyPivotTableWithoutLastRow()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Tabelle3")

    Dim i As Long
    For i = 1 To ws.PivotTables.Count 'loop throug all pivot tables

        If ws.PivotTables(i).name = "PivotTable1" Then 'determine pivot table by its name
            With ws.PivotTables(i).TableRange1 '= full table
                .Resize(RowSize:=.Rows.Count - 1).Copy 'resize to remove last row then copy
            End With
        End If

    Next i
End Sub

That worked perfectly, thanks for the help.I just now need to insert the code to paste data in next available empty row — Tyler L, Jan 10 '19 at 11:37

VBA how to copy to second last cell in table?

1 Answers1