Sheet changes into an array

[jadox]

Hi,

I was wondering if the following were possible:

Can I put inside an array various instances of the same sheet? I mean, for example, Sheet1 with A1 = 10 (this is an instance). I change A1 value to 43 (second instance).
So after various changes I would like all the instances to be remembered into an array so that I could export to pdf the sheet (same one every time - Sheet1) but iterating through all the recorded instances (thus, generating one pdf file, with lots of pages (instances of the sheet)).

Any ideas, suggestions, alternatives?

 

[RickXL]

Hi,

I would probably save the sheet as another sheet. It is quite easy to create new sheets as required.
Copy and paste can be automated, too, in VBA.

Then when you come to print it out everything will be in the same format.

 

[jadox]

Hi,

I would probably save the sheet as another sheet. It is quite easy to create new sheets as required.
Copy and paste can be automated, too, in VBA.

Then when you come to print it out everything will be in the same format.

That's a very good approach but unfortunately if the sheet contains charts and those charts depend on macros for their update (calculation), the copied charts are automatically renamed by Excel in the process, which as you can imagine is not good.

 

[shg]

You could create a data table. See Help for Calculate multiple results with a data table

 

[jadox]

You could create a data table. See Help for Calculate multiple results with a data table

Yes, data tables are good to generate the different instances of the same sheet (I do this differently) but it doesn't solve my export all the generated instances to a single pdf file. This last bit can only be done by exporting a bunch of sheets at the same time, right?

 

[shg]

Not knowing what your sheet looks like, I have no other suggestion.