Shifting cells up/down according to row number without overwriting data.

[Folshot]

Hi everyone,

I am not sure if what I want to do is possible. I have a spreadsheet with 2 worksheets, ie:

- Parts Details: which contains a table of data
- Pivot Table: which is the pivot table based on data from the 'Parts Details' worksheet + additional details.

In the 'Pivot Table' worksheet, the pivot table is contained from columns A to F, then all data beyond column F (i.e. columns G, H and I in this case) are static, ie data which have been entered manually.

Rows are added to the 'Parts Details' worksheet on a weekly basis, the fact of refreshing the pivot table every week makes the pivot table "grows". This is fine however the cells from the columns which are not part of the pivot table (i.e. columns G, H and I) do not shift up or down according to their Product ID in column B.

Example:

I have provided an example in the attached spreadsheet. Basically copy cell range A2 to G7 from the 'Additional Data' worksheet, then paste the it to cell A21 of the 'Parts Details' worksheet (this is to simulate adding rows to the worksheet). Go to the 'Pivot Table' worksheet, right click on the pivot table and select REFRESH.

You can notice that the pivot table is properly updated, however I would have like the data which are not part of the pivot table (i.e. columns G, H and I) to remain associated to their Product ID present in column B. In this example, we can notice that cells G6, H6 and I6 remain in their original position instead of being shifted down respectively to position G9, H9 and I9.

Would anyone know what kind of macro could allow me to achieve the above?


I think the best way would be to (refer to 'Pivot Table 1' worksheet):

1) Copy the data from column B (i.e. Product ID field of the pivot table) to a static column such a column J (note that I could do this manually)
2) Add the data to the 'Parts Details' worksheet (this would be done automatically however you can use the data from the 'Additional Data' worksheet for this test)
3) Refresh the pivot table which adds rows (note that I'll do this manually)
4) Use a macro to:
a) Look for the value from Cell J3 in column B
b) Determine the row at which the value is found in column B
c) Shift cells G3, H3, I3 and J3 to the row determined in previous step
d) Carry on searching for the next value in column B (i.e. cell J4)
e) And go back to step a) above...
The trick would be not to overwrite the data in step c).

I have tried to explain what I would like to achieve, however I am not sure if that makes sense and if it is possible at all. Hopefully it will to someone who's reading this!

Thanks,
Ant