1

Workbook Image

I need to send the excel workbook tabs from "Combined" to "PPA HQ" that contain pivot tables and graphs, without the source data. Is it possible to do that?

I would like to keep the the tabs "Combined" to "PPA HQ" in the same workbook and same formats, but without the source data.

Any assistance is greatly appreciated!

braX
  • 11,506
  • 5
  • 20
  • 33
emald
  • 35
  • 4
  • I believe you can just select, copy, and paste the data to a new workbook! It will look like a pivot table (because the format will copy) but will not actually be a pivot table. It'll just be a fancily-formatted regular table. – Ann L. Dec 27 '22 at 19:28
  • Thank you Ann L, it does not copy the graph though. I need to be able keep both pivot table and graph on all the tabs without the source data so I can send to my customers. – emald Dec 27 '22 at 19:36
  • Is the graph based on the pivot table, or directly on the source data? – Ann L. Dec 27 '22 at 19:37
  • This is kind of a cheat, but one thing you could do would be do a screen snip of your graph (just the graph and the boundary line) and paste that in as an _image_. – Ann L. Dec 27 '22 at 19:38
  • Would it be possible to send your customers a PDF rather than an Excel file, or do they need to be able to interact with the pivoted data (for example, cut and paste it into their own app.)? – Ann L. Dec 27 '22 at 19:39
  • I don't know how many tabs you need to transfer, but you could also try cutting-and-pasting the pivot table, and then re-creating the graph in the target workbook, based on the copy of the pivot table. It would be tedious, but it would get the job done. – Ann L. Dec 27 '22 at 19:43
  • The graph is based on the pivot table. No, unfortunately I need to send it in a workbook and not PDF. I wish I could send it in PDF as it will resolve my source data issue. I need to be able to transfer 11 tabs and I need to do it monthly, so the easiest and fastest way the better – emald Dec 27 '22 at 19:54
  • Hmm. Could you perform an experiment? 1. Make a copy of the workbook to experiment with (don't want to damage anything!) 2. Make a copy of your pivot table and paste it into the same sheet, values-only. 3. Modify the graph so it points to the copy of the pivot table, not the original table. (You can do this by modifying the series references, if you haven't gotten deeply into graphs before). 4. Delete the original pivot table. 5. See if the graph persists/isn't screwed up. – Ann L. Dec 28 '22 at 14:17
  • Another thing you could investigate would be to keep your source data in one workbook, use Excel's data query capabilities to build a pivot table from it in your other workbook (or, just do a summary query: your pivot table looks pretty simple, without many columns), and build your graphs from the data you've pulled into the second workbook. Then you send your second workbook to your users, without sending the source workbook. – Ann L. Dec 28 '22 at 14:21
  • It might also be possible to write a VBA macro to modify the graphs to point to the copied-and-pasted pivot table, but, while I can advise and help with that, I can't give you a formula. I think it's possible, but I haven't done _exactly_ that. – Ann L. Dec 28 '22 at 14:22

0 Answers0