Move Pivot Table row to end automatically

[anne.gomes]

Hi,

So I have this dynamic pivot table that gets added a new row everyday. At the moment whenever a new row is added to it, the new row always gets added below the row with "1 target" on the column B. Because of this I have to manually go inn and move the row with "Target 1" on column B to the end of the pivot table.

I have this code, I want to know how to move the row with the "Target 1" to the end of the pivot table, to its last row.




Sub test()
Dim LR As Long, i As Long
With Sheets("Queue Trend")
LR = .Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("B" & i).Value = "1 Target" Then
'I want to move it to the end
Next i
End With
End Sub




Thanks

[westconn1]

try
.Range("b" & i).EntireRow.Cut .Range("a" & LR)

[anne.gomes]

Thanks westconn1,

What I want to do is just move row "1 Target" to End. I have this code so far...



Sub moveTarget1()
'
' moveTarget1 Macro
'
'

Dim targetOneCell As Range
n = 39
Set targetOneCell = findTarget()
Sheets("Queue Trend").Select
targetOneCell.Select
targetOneCell.PivotItem.Position = 1

Range("B7").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotItems( _
"1 Target").Position = n + 1

End Sub


How do I save n so it increments each day, e.g. today its 39+1 so 40 gets stored as n now and tomorrow it willl be 40+1.

Thank you

[westconn1]

you could store the value in a cell outside of your data area, or on another sheet

or you could use a customdocumentproperty, by adding a new property, then updating it daily, but a cell somewhere is much simpler

[Aflatoon]

Perhaps:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.PivotItems("1 Target").Position = .PivotItems.Count
End With

[anne.gomes]

Thank you to both Aflatoon and westconn1 :)