As the data set is formatted as a proper table the macro will pick up any new data that you add you just need to right clikc in the piot table and refresh.

I downloaded your workbook and all the rows where included, i then added another row and refreshed and again it was included.

Looks fine to me.

Purfleet

If you're trying to only pick up the new data as a pivot table, you can't do that, since the headers aren't adjacent to the data.

Hi Purfleet,

Yes. When refresh the pivot table , can get all the new line added. However, what if I only want to get the new line added do not want to include the existing line. Maybe I should change my question as copy paste instead of using pivot table.

Example:

Week 1 raw data = from row#1 to 20.

Week 2 raw data = add in 4 new line which is row#21 to 24.

Action = to copy week 1 data row#1 to 20 into consolidate worksheet. Then the following week copy the 4 new line row#21 to 24 in into consolidate worksheet.

Purpose = to ensure no changes in row#1 to 20.

Do you think is it possible to use macro? 

Thank you.

CY

Hi CY

In my expirence ANYTHING is possible with a Macro as long as you are willing to find a work around.

As Velouria has said its not really possible, however iif you can identify the new records or are happy to select the new records then you could do something like

Sub Pivot2()

Dim NewData As Range
Dim TitleData As Range
Dim WholeData As Range

Set TitleData = Range("A1:E1")
Set NewData = Selection

'WholeData =

Union(TitleData, NewData).Copy Range("x1")

'Selection.Copy Range("x1")

Set WholeData = Range("x1").CurrentRegion

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
WholeData, Version:=6).CreatePivotTable TableDestination:="Sheet1!R1C14", _
TableName:="PivotTable3", DefaultVersion:=6
' Sheets("Sheet1").Select
' Cells(1, 14).Select
With ActiveSheet.PivotTables("PivotTable3")

End With

ActiveSheet.PivotTables("PivotTable3").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Parts")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("QTY"), "Sum of QTY", xlSum
End Sub

 

Your reveised question, however is very possible, if you can identify the new data - maybe column A to be assigned week 1 and week 2? Then you can get a macro to filter on Week1, copy to consolidated, then delete. The new pivot table on the remaining data would be as per your orginal code i would think

Sub MoveData()

Dim lrCur As Integer
Dim lrCons As Integer
Dim WeekNum As String
Dim r As Range

Worksheets("Current").Activate

lrCur = Cells(Rows.Count, 1).End(xlUp).Row
lrCons = Worksheets("Consolidated").Cells(Rows.Count, 1).End(xlUp).Row
WeekNum = Range("a" & lrCur).Value

Range("A1:f1").AutoFilter field:=1, Criteria1:="<>" & WeekNum

Set r = Range("a1").CurrentRegion.Offset(1, 0).Resize(lrCur - 1, 6).SpecialCells(xlCellTypeVisible)

r.Copy Worksheets("Consolidated").Range("a" & lrCons + 1)

Debug.Print r.Address

Application.DisplayAlerts = False
r.Delete
Application.DisplayAlerts = True

Worksheets("Current").ListObjects("Table1").AutoFilter.ShowAllData

End Sub

Personally i dont like using Pivot tables in a Macro - for me a pivot table is something i do adhoc on a specific dataset, i dont normally used them to display data to other people. They are also at the more advanced end of VBA coding in my opinion and i have spent a lot of time working out how to make them dynamic