Log In
Register
Home
Hi,
Anyone can help me to correct the code how to pick up the only new lines added?
The below is the macro coding to create a pivot table for the entire data from row#1 to 20. What if I have the additional data add in from row#21 to 24, how to allow the macro only pickup the new row added instead of picking up the entire row from row#1 to 24?
Thank you.
Sub Pivot2()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", 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
Trusted Members
December 20, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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
Trusted Members
December 20, 2019
Offline
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
1 Guest(s)
