• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to pick up the new line added?|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How to pick up the new line added?|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to pick up the new line added?
sp_PrintTopic sp_TopicIcon
How to pick up the new line added?
Avatar
Guest
Guest
Guests
1
January 14, 2020 - 5:46 pm
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
January 15, 2020 - 5:17 am
sp_Permalink sp_Print

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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 615
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
January 15, 2020 - 9:46 pm
sp_Permalink sp_Print

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.

Avatar
Guest
Guest
Guests
4
January 16, 2020 - 12:22 am
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
5
January 16, 2020 - 8:00 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard Mander
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.