LinkBack URL
About LinkBacks
Create Pivot Table, Extract Colum/Row/Value Separately
Long story short, I've been using VBA as a side interest that has happened to bring some value to my current role (albeit scripting isn't my full time role) and my employer is now scaling back on non-supported coding as they have a full team in place to do this in an official capacity. However just before the cut off, a team has dropped a request in that I feel is a bit above my level so I'm hoping someone here can help me get this last project over the line before I say goodbye to this aspect of my job.
Summary: Our customers across multiple sites have their user transactions merged into dataset (Sheet: DataSet1). From this customer data, a Pivot Table is required to subtotal all the currencies and Customer References, followed by a line by line breakdown underneath (Example Sheet: DataSet1_Pivot, can stay on same sheet as data when coded) and have those 3 short rows moved to the first available empty row on a separate sheet with pre-saved headers (Sheet: Processing). The sheet will then use a VLOOKUP on the adjacent cells to check the Customer Ref to see which entity it is related to (on a separate ref sheet) and then pull the appropriate payment details across from it in the cells adjacent on the right (I will have these formulas on the sheet already in the final product once I get to that stage).
So far I've coded the MasterFile to import the data from our networked file paths so the DataSet1 information will be ready each day. I have managed to code the Pivot Table aspect but this was based on recording the Macro and recreating it so if anyone is knowledgeable enough to do this it would be a huge help. My main issue is pulling the data from the Pivot Table out and separating by rows as I have done underneath it and then copying it to the Processing Sheet.
I think I can handle the rest from that point but it is just that simple Pivot Table data part that I'm struggling with. The amount of Customers and Currencies used each day could be lots of very few so it needs to dynamic in how it loops through. I've made the dummy data as bland as possible but I'll have to rename a lot of columns etc when I move it to my work project file.
Any help would be massively appreciated, as I've said I will be moving away from this in a work capacity soon but this is one of the last few things I need to get over the line. I've been given til the end of this week to get it cleared so it is stressing me out a bit.
Register To Reply
Bookmarks