Copy Chart to Another Workbook VBA help please

[longbow007]

Hello, could someone please help me in trying to do the following in Excel 2003?

Please refer to VBA code below which can be found in Module1 to the C-27J Outstanding Spares Requisitions.xls spreadsheet that karedog kindly provided that copies the C-27J Outstanding Spares Requisitions.xls spreadsheet --- Chart1 worksheet --- Pivot Table to the Archived_Charts.xls spreadsheet --- Charts1 worksheet.

I need similar VBA code please to copy the associated Chart from the C-27J Outstanding Spares requisitions.xls spreadsheet --- Chart 1 to the Archived_Charts.xls spreadsheet --- Charts1 worksheet.

So, I need a macro when ran for the first time, to paste the copied Chart into the
Archived_Charts.xls spreadsheet --- Charts1 worksheet in cell D4.

When the macro is re-run, I needs to select the next empty cell in the
Archived_Charts.xls spreadsheet --- Charts1 worksheet in column D that are 45 rows down from cell D4.

That is cell D49.

So, every time the macro is run, it needs to paste the copied Chart in the next empty cell in the Archived_Charts.xls spreadsheet --- Charts1 worksheet in column D that are 45 rows down from the last pasted data.

So, in Archived_Charts.xls spreadsheet --- Charts1 worksheet, the series of cells that would be used to paste the copied Chart into each time this macro is run would be: A49, A94, A139, A184, A229, A274, A319, A364 etc..

Please Login or Register  to view this content.

Any help would be greatly appreciated.

Kind regards,

Chris

[karedog]

Hi Chris,

Some matters to clarify for the previous case :

- When you mean "copy", do you mean "copy" as "copy as formula" or "copy as value" ? My previous code acts like "copy as formula", so if you examine the pasted data on A4, A49, A94, etc, they all are exactly the same object. If you intend to take "incremental snapshot" (so each pasted values are different each time you change the source data) then the code must be modified to "copy as value".

- As I said in previous case, using my first code (which you included in the workbook on this thread), the targetRow variable is reset each time you close workbook or the macro is reset (for example, modified/add/delete a macro will do the reset), and each time the targetRow variable is reset, the code start pasting from row 4 again. If you want to preserve the targetRow, even the workbook is closed, or the macro is reset, then you must use my second code.

After I rethink, you probably want to "take incremental snapshot", so the data need to be static (pasted as value), so the code is needed to modified if this is the case.
I will try to help you on this thread, but let's clarify the matters above first.

[longbow007]

Hi karedog,

Thank you for your reply - much appreciated.

- to help clarify, your code that I placed into Module1 in the C-27J Outstanding Spares Requisitions spreadsheet is used on the 28th day of each calender month. So on that day, whatever data is showing (snap-shot) in the Chart1 worksheet --- Pivot Table needs to be copied across to the Archive_Charts.xls spreadsheet --- Charts1 worksheet and pasted into column A at cell A4 in the first instance. The associated Chart found in C-27J Outstanding Spares Requisitions spreadsheet --- Chart1 worksheet also needs to be copied into the Archive_Charts.xls spreadsheet --- Charts1 worksheet and pasted into column D at cell D4 in the first instance. On the 28th day of the following calender month, I need the macro to repeat this process but this time paste the Pivot Table in column A at cell A49 and paste the associated Chart in column D at cell D49.

- So, each time the macro is run, it pastes the Pivot Table and associated Chart in the next empty cells 45 rows down from the last paste in columns A and D respectively.

- You are correct in saying it is an incremental snapshot.

- I am sorry for not pointing that out the first time I created the post --- it is often difficult articulating to someone what I am trying to do with Excel.

- I hope this is clearer, if not please let me know.

Kind regards,

Chris

[karedog]

Hi Chris, thanks for the explanation.

Since it is cleared that you want to take "incremental snapshots", so the code would be something like this :
(please remove all previous code first, this macro will copy both the table and the chart as "static values", which is the case for "incremental snapshots")

Please Login or Register  to view this content.

[longbow007]

Hi karedog, thanks so much for all your hard work and efforts in producing this VBA code - it's amazing and it works very well.

Kind regards,

Chris

[karedog]

You are welcome, glad I can help.


Regards