• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

EXCEL automatic duplicate range

Hello.
I’m working on an EXCEL file.
Is it possible
•      to make a sheet be the automatic copy of another sheet?
•      To make some column on a sheet to be the automatic copy of a column on another sheet?
The source sheet is a sheet on which lines are deleted and inserted. I hope the destination sheet be automatically refreshed when the user deletes or inserts a line on the source sheet.

Be using a button with VBA code behind?
Is there a solution without VBA code?

Thank you very much for your help.
0
molune
Asked:
molune
  • 2
  • 2
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Is the "automatic" copy needed after every single change to the source worksheet?

The worksheet (or even the workbook) could simply be copied in entirety.

It is a lot of work/time (& processing at run-time) to track every single change to a worksheet just to mirror this elsewhere, so your reason(s) for the necessity for the "automatic" copy would be helpful.

I have to wonder why you would want an exact copy of a worksheet.  Having a copy one step behind the current version would seem more practical (so you can reinstate that if an error has been made in the original).

Thanks for your clarification of your intentions.

Mirroring a single column, however, is faster but not quite as accurate (depending on what changes are made) as not all changes will be able to be tracked on a single column basis; font names/sizes, for instance.

BFN,

fp.
0
 
moluneAuthor Commented:
Hello fanpages.
Thank you for your answer.

The mirroring concerns values (numbers or text), calculated or not calculated. I don't want to track font names or sizes changes.
I want the destination sheet be automatically refreshed when the user is working on the source sheet, when he deletes or inserts a line on the source sheet. If possible, the user who fills the sheet has to do nothing in order to change the destination sheet.

So ...
Is that done by using a button with VBA code behind? (the user does something but ...  just clicks ... or is there a better event to be tracked?)
Or is there a solution without VBA code (I would prefer)?

Thanks

ml
0
 
[ fanpages ]IT Services ConsultantCommented:
I am still not clear why a worksheet, or a column of data from a worksheet, would need to be mirrored in this manner.  What are you hoping to achieve?  Understanding the requirement means one or more suitable solutions can be proposed.  Not understanding your intentions may mean that potential pitfalls are missed.  It may be the case that the purpose of mirroring data can be achieved by other (easier, more robust, quicker) methods, for instance.

This aside, if mirroring just a single column or specific range, then inserting or deleting rows would impact the rest of the (destination) worksheet where the mirrored data is being stored.

Yes, a button can be provided as a semi-manual process, or the event can be fully-automated when an insertion or a deletion of a row is detected.

As I am sure you are aware, mirroring data from one worksheet to another is usually performed by just referencing the source cell within the destination cell with an in-cell formula.  However, the inserting &/or deleting of rows from the source worksheet (or range) that will cause issues if the in-cell formulae results in #REF! errors.

A solution without VBA code (as a fully manual process) would be simply to copy the source worksheet to another worksheet (either within the same workbook, or in another workbook).

I am presuming that the mirrored/destination worksheet is within the same workbook as the source worksheet/data.   Is that the case?
0
 
moluneAuthor Commented:
Thank you again.

Not understanding your intentions may mean that potential pitfalls are missed.  It may be the case that the purpose of mirroring data can be achieved by other (easier, more robust, quicker) methods, for instance.

YES ... of course. I shall be careful when using your recommendations.
Sometimes, you just want to do some calculations using a column calculated elsewhere in a (source) sheet and SEE that column in another (destination) sheet  before doing other calculations on it.

As I am sure you are aware, mirroring data from one worksheet to another is usually performed by just referencing the source cell within the destination cell with an in-cell formula.  However, the inserting &/or deleting of rows from the source worksheet (or range) that will cause issues if the in-cell formulae results in #REF! errors.

YES, that is the problem. As you can reference the source cell within the destination cell, I wondered whether it could be possible to "reference a source column within a destination column" ... that is not possible, that's it?

A solution without VBA code (as a fully manual process) would be simply to copy the source worksheet to another worksheet (either within the same workbook, or in another workbook).

YES, that is what is done now. But I want a more automatic process.

I am presuming that the mirrored/destination worksheet is within the same workbook as the source worksheet/data.   Is that the case?

YES

A button can be provided as a semi-manual process,
So ... I  code a macro to copy the desired column or the desired range and launch it when the user clicks on a button

the event can be fully-automated when an insertion or a deletion of a row is detected.
OK but I need to take into account that the user just fills a cell.
Is there an event for that or Is the following solution good?

Solution:
- catch an insertion or deletion of lines
- but instead of copying the column Columns("X:X").Select     Selection.Copy
write a formula in each cell to reference the source cell within the destination cell with an in-cell formula (=!source.Xi for example). But
Is that done in VBA with a loop on cells or is it possible to define a formula for the column?

Thank you for all ...

ml


Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now