CODEWITHSUNDEEP
c#vbaexcelpivot-table
MoralesJosue
MoralesJosue

Reputation: 199

Copy values from pivot table to sheet in specific cell

I have some data that is displayed in a pivot table, from that pivot table I currently copy and paste them in another table with the corresponding current day, with so many operation numbers it takes about 1hr to complete this process daily, I'm trying to make a macro in which will help me in making this process in less time

This is the pivot table in which I copy the data

Pivot Table

and here is where I paste it (in this table I use it to create some graphs)

enter image description here

Both tables consist of many different numbers of operation, as you can imagine this is very tedious.

Any help or ideas that can help me solve this is appreciated, I'm willing to try some other alternatives like fomulas, vba (preferred), even closedxml for c#.

I dont know if im explaining my self? This is my logic.

  1. Look for operation number
  2. Copy Sum of "Sx"
  3. Look in other sheet for the same operation number.
  4. Paste value in corresponding day.
  5. Look for same operation number with next "Sx"
    .
    .
  6. End

Right now I'm just focusing on Operation1, since in Operation2 I use another sheet in which I do the same thing. (will probably use the same code as Operation1).

To clarify I'm not asking for someone to just give me the solution (although it is appreciated ), but any guide on this is helpful.

Upvotes: 1

Views: 1888

Answers (1)

L42
L42

Reputation: 19727

You do not need VBA to do this. Use GETPIVOTDATA.

Consider below replicated data:

enter image description here

In above sample, I copied some of your data in your screen shot, made a pivot out of it, put it in the same sheet and also put the table you need to populate below it. Then I use GETPIVOTDATA. I feel I need to show you how it is done even though it is well explained in the link I posted.

So we use this formula:

=GETPIVOTDATA("Sum of "&$I18,$I$2,"Date",J$17,"Opt",$I$16,"Id",$J$16)

in Cell J14. Take note that instead of using Day 1, Day2.. etc on your destination table, we used the actual date. Why? Because we need that in our GETPIVOTDATA Formula. Then copy the formula to the rest of the cells.

Result:

enter image description here

Now, GETPIVOTDATA errors out if it does not find anything which matches the criteria you supplied, so you might want to incorporate error handling using IFERROR statement.

Final Formula:

=IFERROR(GETPIVOTDATA("Sum of "&$I18,$I$2,"Date",J$17,"Opt",$I$16,"Id",$J$16),0)

Although you prefer VBA, I don't think a better approach than the built-in Excel functionality is more suitable in your case.

Upvotes: 1

Related Questions