Copy data from one worksheet to another

[Sparrkky]

Worksheet 1 has data that will always stay in there appropriate cell address
and be available for only one person to update. Worksheet 2 will be a copy of
Worksheet 1 but only used to sort by criteria in a given column and only be
used for sorting purposes by more than one person. Any data that is changed
in Worksheet 1 will automatically change the data in Worksheet 2.

How can I accomplish this? The "=Sheet1!$A$1" function is not feasible since
I am over 150 columns wide and at least 1000 rows deep. I am sure there is
some other way to accomplish this task!

 

[Shane Devenshire]

Hi,

It really depends on your data layout. One option would be to create a
pivot table from the data on the first sheet and place it on the second one.

 

[Otto Moehrbach]

Since sheet 2 is used for sorting, it follows that sheet 2 is not a copy of
sheet 1, therefore updating a cell in sheet 2 whenever that same cell is
changed in sheet 1 is not an option.
You will need VBA programming to do what you want. The first macro below
will fire whenever any change is made to any cell in Sheet1. When that
happens, this macro will clear the entire Sheet2 and will then copy the
entire Sheet1 to Sheet2.
This macro is a sheet event macro and must be placed in the Sheet1 module.
To access that module, right-click on the Sheet1 tab and select View Code.
Paste this macro into that module. "X" out of the module to return to
Sheet1.
Depending on how and when and how many times Sheet1 is updated, copying the
entire sheet (what this macro does) each and every time that any cell in
Sheet1 is changed may not be very efficient. You may want to run the macro
yourself by, say, clicking on a button after the updating of Sheet1 is
complete. If you want to do that, place the second macro shown below in a
regular module , place a button on Sheet1 and assign that macro to that
button. Post back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").Cells.ClearContents
Cells.Copy
Sheets("Sheet2").Range("A1").PasteSpecial
Application.CutCopyMode = False
End Sub

Sub UpdateSheet2
Sheets("Sheet2").Cells.ClearContents
Cells.Copy
Sheets("Sheet2").Range("A1").PasteSpecial
Application.CutCopyMode = False
End Sub

 

[Teethless mama]

Try this:

Right click on Sheet1 > select move or copy > tick on Create a copy > OK

 

[Ashish Mathur]

Hi,

Why not =Sheet1!A1 and then copy down and right

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com