How do I stop pasted cells updating when the source cells are chan

I have a worksheet that contains a large array of data on one tab, organised 
into a seperate column of test results for each of several characteristics.
I've created a macro for each of these characteristics that copies the 
results into another tab, which in turn becomes the source data for a Report 
sheet that includes a couple of charts. This allows me to run an individual 
report for each of the characteristics in turn.
However, I've just created another macro that opens a new workbook, then 
runs each of the individual macros in turn and copies the Report Sheet into a 
new tab in the new workbook.
This works fine EXCEPT..... because the content of each new tab has been 
copied from the same Report Sheet tab, each time this tab changes, they all 
update and end up with identical data on them.

My question is...... If you copy data from one worksheet to another 
(including Charts), then update the source, how do you stop the pasted cells 
from updating automatically?
0
Sntee (1)
6/19/2006 3:04:03 PM
excel 39879 articles. 2 followers. Follow

1 Replies
415 Views

Similar Articles

[PageSpeed] 54

You need to record, Edit>Paste special+Values (Or values and number 
formats). This will kill the formulae at the destination

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Sntee" <Sntee@discussions.microsoft.com> wrote in message 
news:9E037C16-4B5A-43DE-8304-BEB8FF0901BD@microsoft.com...
>I have a worksheet that contains a large array of data on one tab, 
>organised
> into a seperate column of test results for each of several 
> characteristics.
> I've created a macro for each of these characteristics that copies the
> results into another tab, which in turn becomes the source data for a 
> Report
> sheet that includes a couple of charts. This allows me to run an 
> individual
> report for each of the characteristics in turn.
> However, I've just created another macro that opens a new workbook, then
> runs each of the individual macros in turn and copies the Report Sheet 
> into a
> new tab in the new workbook.
> This works fine EXCEPT..... because the content of each new tab has been
> copied from the same Report Sheet tab, each time this tab changes, they 
> all
> update and end up with identical data on them.
>
> My question is...... If you copy data from one worksheet to another
> (including Charts), then update the source, how do you stop the pasted 
> cells
> from updating automatically? 


0
6/19/2006 5:44:44 PM
Reply:

Similar Artilces:

XML 2004 just 3 weeks away -- view program updates -- reserve room -- register today
XML 2004 Conference & Exposition –“From Syntax to Solutions” November 15-19, 2004 – Marriott Wardman Park Hotel, Washington, D.C. http://www.xmlconference.org XML 2004 is just 3 weeks away. The largest continuously running, independently organized XML conference, XML 2004 is produced by IDEAlliance http://www.idealliance.org and partnered by other industry and standards –making organizations, such as OASIS, W3C, WS-I, NISO, DISA, and LISA. XML 2004 taking place November 15-19 at the Marriott Wardman Park Hotel in Washington, D.C. offers over 140 presentations, 30+ exhibitors, to...

Rollong back a Windows Update?
How do I uninstall a Windows update if I can't find it in the list of $NTUninstallKBxxxxxx files in my Windows folder? "jetjock" <jetjock@hanger.com> wrote in message news:ht8il5l5j0gq21mdeod5bf4p6uqpoftjcf@4ax.com... > How do I uninstall a Windows update if I can't find it in the list of > $NTUninstallKBxxxxxx files in my Windows folder? Remove it via Add/remove Programs in the Control Panel. Make sure the Show Updates box is checked at the top of the page. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA Bruce Ha...

How do I disable the pop-ups that appear when I copy/paste, etc?
I just upgraded to Office 2003. Each time I copy, paste, fill down, etc., I get these little pup-up wizards. What are they actually called? How do I disable this feature? <Tools> <Options> <Edit> tab, And uncheck the options you don't wish to have displayed. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Fiscal Al" <Fiscal Al@discussions.microsoft.com> wrote in message news:8C10E796-C820-434C-94B6-3660D549...

Multiple choices for a cell
I am creating a worksheet which deals with employee or job candidates qualifications. Each cell will hold the various details pertaining to a person's qualifications. Different people have different degrees. This will be all stored in a single cell and separated by a comma. Is it possible to create some sort of custom or drop down box which contains controls or check boxes so as to create a standardised list of qualifications the data entry clerk can choose from? For example, i would like a cell to have a drop down box, which is a field that displays the qualifications a particular gra...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

CRM 3 SO slow after applying update rollup 2
We have been using CRM 3 for over a year. I found it to be slow, but we lived with it. I applied UR 2 to our CRM server. I open the CRM client to our contact list (we only have 4500 contacts in total) and it takes 4 minutes til the first 250 contacts are displayed. UR 2 cannot be uninstalled. WhY does this make things worse instead of better? We barely have any data in CRM (database is only 230mb) so why is it soooooooo slow?! ...

displaying a comment only when the cell is selected #2
I'd like to insert a comment in a certain cell, so that when that cell is selected by a user, the comment is then displayed. I know this must be possible, but I haven't been able to figure it out.... I can show or hide comments in various combinations, but can't find a way to hide the comment UNTIL the cell is selected, and then display it. How do I do this please?! Thanks Hi Jeff, Try: Tools Options | View tab | select 'Comment indicator only" | OK --- Regards, Norman "Jeff" <no_em@ilplea.se> wrote in message news:JdidnecOi7ehGVbfRVnyhg@eclip...

exchange 2003 public folder sync, which one is updated
If I have public folders replicateing between 2 exchange 2003 systems and someone makes a change, which one is updated? The one they are connected to is the one that is updated. Users are connected to the one referred to in the properties of the Mailbox Store that houses their mailbox. "Trey" <Trey@discussions.microsoft.com> wrote in message news:271997FD-E0BD-469B-A64D-2FC52FB68575@microsoft.com... > If I have public folders replicateing between 2 exchange 2003 systems and > someone makes a change, which one is updated? ...

Last Cell #2
I know this has been asked before but I cant find what I want on Google. I need to get the address or row number of the first empty cell in column A, either will do, I need to use VB to copy and paste data from ten sheets into one. A formula or a VB way would be great, anyway of identifying the first empty cell. This cell may be empty or contain a null string. Any help would be reaaly good. TIA Alan. to find the last row x=cells(rows.count,"a").end(xlup).row+1 to find the next empty x=cells(1,1).end(xldown).row ' or address -- Don Guillett SalesAid Software donaldb@281.com &...

duplicate cells linearly?
Hello all - Is there a way to duplicate a formula in cells were the row reference grows linearly, instead of simply the number of cells? For instance, if you have in Sheet2 the formula Sheet1!$c1 and you duplicate it into rows below, you get Sheet1!$c2 Sheet1!$c3 Sheet1!$c4 Sheet1!$c5 However, we would like to do : Sheet1!$c4 Sheet1!$c8 Sheet1!$c12 We have a spreadsheet composed of two worksheets. In the first worksheet there are cells which are summed every 72 rows. We want those summed values to appear in worksheet 2, in one row after the other. Since we have a lot of summed cells, i...

Loan numbering stops
Loan numbering has stopped when I last changed my payment amount due to escrow recacluation. I get this error attempting to manually assign them: "You cannot change this transaction into an electronic payment or trnasfer because the date is not within an acceptable range and cannot be changed" How do I get the numbering restored? ...

Cell Not working
I have office 2002, my windows is home xp. Last week we had to reformat my xp, got a hold of a nasty worm! I just use a chart to display numbers in series. Now when I try it from an old sheet it inserts the number as a date? I keep trying to change the cell to GENERAL, but it dosen't work. Can't understand it. When you say you keep trying to re-format the cell as General, what result do you get? If I do it on a date cell (re-format) I get the date code (i.e. the value of the date). Ex. cells has "03-01-11", select cell and re-format to General = 37632. -- Regards; Ro...

Pivot Table
I have 2 lists of 100 items (rows) with 5 data elements. Each list has a common element, which can tie the two lists together. How can I combine these 2 lists and use it for the source data of a pivot table? Todd ...

delete row if given cell = 'X'
Is there an easy way to do the above. Thanks much Carl "MSNews" <nf@no.com> wrote in message news:%23Fj6fBvPKHA.764@TK2MSFTNGP02.phx.gbl... > Is there an easy way to do the above. > > Thanks much > > Carl > I'm not an expert but use data filter to select the rows, select and delete them. Try it with a trial sheet first though! Peter Several ways 1. data>filter>autofilter>filter on the x>delete 2. a looping macro from the bottom up sub delifx() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if lcase(cells(i,&qu...

How do I stop numbers from converting into dates?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I want to enter 4/1 or 4-1 into a cell but every time I press enter it converts it to 1-Apr. this is very irritating and despite looking in the the various preferences and format tabs I have yet to find a way to stop it. What am I doing wrong? First of all, any formulas need to be preceded by an equals sign (=) to let Excel know that what follows is an 'instruction' to be executed. Otherwise the content is interpreted as a text string or -- as in this case due to the operators you're using ("/&quo...

using two cells 1 with total time n 1 with date how can i put the.
I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2 column E ) from a sheet that has the dates in one cell (column A) and the time spent in another (column K) and the reason for the time spent in column B. On the seperate sheet I have to put the amount of time spent in column E and match with the date I have in column A on the seperate sheet to populate the chart that I'm making. The only thing that I have been able to do is create a formula to show the amount of time spent and for what reason but i can't match it to the date because the dates ...

how to prevent a formula in a summary sheet from automatically updating when a row is addedto the referenced sheet
I have a workbook set up that consists of several sheets and a summary sheet that refers to the third row on every other sheet. The worksheets that are referenced contain dated information that is sorted with the most recent entries entered in row 3 and these are the entries I need to track in the summary sheet. When I insert a new row 3 in these worksheets, the formula in the summary automatically updates to line 4 and I need it to always reference row 3, the newest entry. This should be simple but I cannot find the solution. Any ideas? Thanks, krisp1950 You could use the INDIRECT fun...

print title page setup/sheet can not enter cell in row/column lin
On page setup in the sheet page we can not enter cells into the print titles row or columns. Also the worksheet cell query on these lines will not open. Hi Are you trying do this from Print Preview page, or File|Page Setup? I think it's only possible from the latter. -- Andy. "don r" <don r@discussions.microsoft.com> wrote in message news:26C400D7-14A0-41F8-BBE6-8DF2F5579A30@microsoft.com... > On page setup in the sheet page we can not enter cells into the print > titles > row or > columns. Also the worksheet cell query on these lines will not open. &g...

How do I paste several files together for comparison?
I created seven files that are related to each other and i wanted to put them together in comparison so how do i put these files togther. For example: File#1 is in colum A B C D and i wonder if i could put file # 2 in E F G H....and so on. Thank you. Data all the same in each file? If so then create a single file as follows A B C D E File Hdg1 Hdg2 Hdg3 Hdg4 File1 Col1 Col2 Col3 Col4 File1 Col1 Col2 Col3 Col4 File1 Col1 Col2 Col3 Col4 File2 Col1 Col2 Col3 Col4 File2 Col1 Col2 Col3 Col4 File2 Col1 Col2 Col3 Col4 File3 Col1 Col2...

Office 2004 security/stability update to 11.3.4 Feb 13 changes Word version
The only difference I can find is that the version number for Word has changed from 11.3.3 to 11.3.4. Entourage and Excel remain at 11.3.3. So far, all three components play nice with OS 10.4.8 on a Dual G5 2.0 I ran the updater from the Entourage/Help/Check for Updates pulldown menu. No permissions problems were found after the update. Respectfully, Norm The 11.3.4 update is a security update and it affects three specific components of Microsoft Office 2004: Microsoft Word 2004 is updated, as are the Junk E-Mail Protection and Microsoft Component Plugin files (both of which are in Appl...

Shortcut to Reset/Stop debugger
Hello, I use F5 and F8 to run and debug my code, is there a key that will stop the debugger? I always feel like 'Esc' should do it, but it doesn't M F5 will run the code so the only way to stop, or rather 'break' is with ctrl-Break or Esc. If you then press Enter you will be in debug mode as if you had started with F8. In debug mode to stop or reset, apart from clicking the button you could do - Alt-r, r (might be different in non-English systems) Regards, Peter T "Michelle" <mh_londonNOJUNK@hotmail.com> wrote in message ...

lowest Digit Cell...
hello what's the formula to use when i have 20 cells with digits & i want to get the lowest digit on those 20 cells, in a different cell ? --- Message posted from http://www.ExcelForum.com/ i ment like If A1 is 8.02 A2 is 8.24 A3 is 9.79 A4 is 7.09 A5 is 7.15 & Cell C1 suppose to Show 7.09 --- Message posted from http://www.ExcelForum.com/ One way: C1: =MIN(A1:A5) In article <Pivotrend.ysouy@excelforum-nospam.com>, Pivotrend <Pivotrend.ysouy@excelforum-nospam.com> wrote: > i ment like If > > A1 is 8.02 > A2 is 8.24 > A3 is 9.79 > ...

How do I compare cells in a column
I am inputing a large column of names and numbers and would like to insure I don't repeat any. I'm not sure how the Vlookup works I continually receive errors and the If command only works with 2 cells. Thank you in advance for any help!! One way .. Suppose the list to be entered is in col F Select col F Click Data > Validation Make the settings: Choose under "Allow:" : Custom Source: =COUNTIF($F$1:F1,F1)<=1 Click OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Jim K" <Jim K@discus...