Solved

EXCEL automatic duplicate range

Posted on 2013-06-05
4
151 Views
Last Modified: 2014-02-10
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
Comment
Question by:molune
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
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
 

Author Comment

by:molune
Comment Utility
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 300 total points
Comment Utility
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
 

Author Comment

by:molune
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now