Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sparrkky" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
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 "Sparrkky" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Try this:
Right click on Sheet1 select move or copy tick on Create a copy OK "Sparrkky" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Hi,
Why not =Sheet1!A1 and then copy down and right -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Sparrkky" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy data from one worksheet to another | New Users to Excel | |||
copy from B worksheet to A worksheet with NO repeated data | Excel Discussion (Misc queries) | |||
copy data from a worksheet | Excel Worksheet Functions | |||
copy data in a cell from worksheet A to worksheet B | Excel Discussion (Misc queries) | |||
PLEASE HELP......COPY DATA FROM ONE WORKSHEET TO ANOTHER | Excel Worksheet Functions |