Find Second Blank Row after a Pivot Table

[SAMCRO2014]

Hi! I have been working on this macro for over a month and now I am on the home stretch. I just need help with one more thing (I hope). I need to be able to find the second blank row after a pivot table to input some text. The size of the pivot table will change every time it is refreshed.

I hope someone can help me!!!! I have googled as much as I can on this with no luck. I can do it if it was just data but where it is a pivot table, it is giving me difficulty!!!

 

[SpillerBD]

The second row below the Pivot Table would be, where "A1" is a cell for a series in the table that is continuously populated.

LastData_Row_Count = Range("A1").End(xlDown).Row[COLOR=#0000ff]+2[/COLOR]

The other typical method is

FinalRow = [I]SheetName[/I].Cells(Rows.Count, 1).End(xlUp).Row[COLOR=#0000ff]+2[/COLOR]

Using this method assumes that there is already nothing below the PivotTable.

 

[SAMCRO2014]

I am having an issue with the pasting part of the marco::

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Selection.Copy

With Data
FinalRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row +2
.Range("A" & FinalRow).Paste
End With

It does not like the ".Range("A"& FinalRow).Paste"

Any suggestions?

 

[Peter_SSs]

I am having an issue with the pasting part of the marco::

What are you actually trying to do in the code?
What is 'Data"? How is it declared and assigned?


To put some text 2 rows below your pivot table, no matter where it is or what else might be below, try

With ActiveSheet.PivotTables("PivotTable1").TableRange1
  .Offset(.Rows.Count + 1).Cells(1).Value = "Some text"
End With

 

[SAMCRO2014]

I want to copy and paste "PivotTable1" on the "Data" worksheet to two lines below where "PivotTable1" ends. I am hoping I can just change the filters to create a different pivot table with the same data.

 

[Peter_SSs]

I want to copy and paste "PivotTable1" on the "Data" worksheet to two lines below where "PivotTable1" ends. I am hoping I can just change the filters to create a different pivot table with the same data.

Is this what you mean then?

Sub Copy_PT()
  With Sheets("Data").PivotTables("PivotTable1").TableRange1
      .Copy Destination:=.Offset(.Rows.Count + 1).Cells(1)
  End With
End Sub

 

[SAMCRO2014]

That worked awesome!!! Thank you!!!!!!!

 

[Peter_SSs]

That worked awesome!!! Thank you!!!!!!!

You're welcome. Thanks for letting us know.