VBA to find last filled cell and move down, when not all cells are filled

[jrc999]

I’m getting back into VBA after several years away. Refreshing my memory is harder than I thought. I’m creating multiple pivot tables from the same data sheet (“SOW Detail”), and inserting them one after another on “Sheet2”, with 3 empty rows between them. The macro recorder is not capturing the steps to find the last filled cell and come down 4 rows. It’s only capturing the result as the location for the new table—e.g., “Sheet2!R78C1” in the code below. To determine that cell, I’m selecting AA1, and doing the following sequence, then moving down 4 rows, to leave 3 blank rows between tables. How can I incorporate the "Selection.End(xl..." steps and moving down 4 rows into the code to create the pivot tables? Note, the number of rows in each table can vary.

Code to find last filled cell:

    Selection.End(xlDown).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select

Code to create pivot table:

    Sheets("SOW Detail").Select
   ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable3").PivotCache._
        CreatePivotTableTableDestination:="Sheet2!R78C1", TableName:="PivotTable4"_
        , DefaultVersion:=xlPivotTableVersion15
    Sheets("Sheet2").Select
    Cells(78, 1).Select
    WithActiveSheet.PivotTables("PivotTable4").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable4").PivotFields("Location")
        .Orientation = xlPageField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable4").PivotFields("RoomType")
        .Orientation = xlRowField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable4").PivotFields("RoomType")
        .Orientation = xlColumnField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable4").PivotFields("SolutionFamily2")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataFieldActiveSheet.PivotTables( _
       "PivotTable4").PivotFields("Qty2"), "Sum ofQty2", xlSum

Thank you so much!

 

[ParamRay]

Try something along these lines:

Sub Macro1()
  Dim p1 As PivotTable
  Dim p2 As PivotTable
  Dim r As Long
  Dim c As Integer
  
  Set p1 = ThisWorkbook.Sheets("Sheet2").PivotTables("PivotTable3")
  r = p1.TableRange1.Item(p1.TableRange1.Count).Row + 4
  c = p1.TableRange1.Column
  
  Set p2 = p1.Parent.PivotTables.Add( _
    PivotCache:=p1.PivotCache, _
    TableDestination:=p1.Parent.Cells(r, c), _
    TableName:="PivotTable4" _
  )
    
'  p2.PivotFields("Location").Orientation = xlRowField
'  p2.PivotFields("RoomType").Orientation = xlColumnField
'  etc.

End Sub

 

[jrc999]

Thank you so much for your help!

When I run this code, I get the following:

Run-time error '1004': Unable to get the PivotTables property of the worksheet class

for the line

Set p1 = ThisWorkbook.Sheets.("Sheet2").PivotTables ("PivotTable3")

I tried this on both a fresh "Sheet2" and after running the first pivot table and putting it on Sheet2.

Any thoughts on what I'm doing wrong?

Thank you again!!

 

[ParamRay]

Any thoughts on what I'm doing wrong?

You need to make sure the names of the objects in the code match precisely with their names in Excel.

 

[jrc999]

Very helpful--thank you so much!!