Copy formulas via Code

I have a range with formulas on sheet2 called "database". I want to get this 
range and transfer it's contents to sheet1 called "work", but I want the 
formulas to update themselves to this new location, and not show the same 
exact formula that was found in the original range. I had tried :

sheets("work").range("a1:b10").formula = 
sheets("database").range("d1:e10").formula

 with the following results

Cells d1 to d10 on sheet database contain X values, and cells e1 to e10 
contain the formula =d1*d1, =d2*d2, and so forth.

What I obtain on cells a1 to b10 on sheet work is exactly the same formulase 
outlined above ( ie : = d1*d2 ) instead of the formula getting updated to = 
a1*a1, = a2*a2.

I don't want to use the copy paste method because it changes the sheet 
selection and I have code written for the activate and deactivate events on 
each of these sheets.

Any suggestions?
0
Cordobes (8)
12/11/2005 2:38:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
472 Views

Similar Articles

[PageSpeed] 55

One way:

    Sheets("work").Range("a1:b10").FormulaR1C1 _
        = Sheets("database").Range("d1:e10").FormulaR1C1

Another way is to copy|paste special|formulas



Cordobes wrote:
> 
> I have a range with formulas on sheet2 called "database". I want to get this
> range and transfer it's contents to sheet1 called "work", but I want the
> formulas to update themselves to this new location, and not show the same
> exact formula that was found in the original range. I had tried :
> 
> sheets("work").range("a1:b10").formula =
> sheets("database").range("d1:e10").formula
> 
>  with the following results
> 
> Cells d1 to d10 on sheet database contain X values, and cells e1 to e10
> contain the formula =d1*d1, =d2*d2, and so forth.
> 
> What I obtain on cells a1 to b10 on sheet work is exactly the same formulase
> outlined above ( ie : = d1*d2 ) instead of the formula getting updated to =
> a1*a1, = a2*a2.
> 
> I don't want to use the copy paste method because it changes the sheet
> selection and I have code written for the activate and deactivate events on
> each of these sheets.
> 
> Any suggestions?

-- 

Dave Peterson
0
petersod (12005)
12/11/2005 2:51:46 PM
Reply:

Similar Artilces:

what is the html code for bold
I am adding html code to my web pages, using Publisher 2003. I have added html code to direct the hyperlink to a new window, and have text around the hyperlink that I want to bold. (Bold some of the surrounding text, not the hyperlink). I am brand new at this. Anyone know the html code to add? Thanks for your help http://www.html-and-css-tutorial.com/tutorial-3.html -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Rob T" <Rob T@discussions.microsoft.com> wrote in message news:B4EE0AB4-6B88-4E09-AD25-347CFC2BF...

Formulas #24
How can I programme excel so that if Column A is filled in for a value then it will ignore any values in Column B Hi see your other post -- Regards Frank Kabel Frankfurt, Germany GBH99 wrote: > How can I programme excel so that if Column A is filled in for a > value then it will ignore any values in Column B ...

Color code quoted text
Is there no way to color code quoted text in the standard message reading view? I've searched high and low and can't seem to find anything about this. The newsreader in Opera does a terrific job of this, as do many other programs. Bob Graham Yes, but since you decided to keep secret the version of Outlook you are = using, all bets are off. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, rvgrahamsevatenein@sbcglobal...

Report writer
I have 'rewritten' SOP BLANK INVOICE HISTORY FORM. I have substantially modified it. I am now happy with it. Is there any way I can copy/ paste/export/ import this in such a way that I can replace SOP BLANK INVOICE FORM i.e. I am trying to avoid having to do it all again in SOP BLANK INVOICE FORM starting at square 1. If I understand you correctly you simply want to change the name of the report. This can be done if you use Tools -> Customize -> Customization Maintenance to export to a package file, then edit the package file in notepad or any other text editor. Save a...

Automated Item lookup code
Dear All, Is there anyway of automating the item lookup code when you are adding a new item based on previous codes in that particular department/category/line, etc. Instead of the employee inputting the code themselves can it be generated automatically for every new item that you add. Thanks, Faiz. We have a add on Module to do exactly this http://www.retail-systems.net/ where are you located Middle East "RSN" wrote: > We have a add on Module to do exactly this > > http://www.retail-systems.net/ > > where are you located > > Faiz, If you are int...

Average formula #2
I need a formula that would look at a month's worth of information and throw out any info that is unusual - (st.Deviation?) I really only want business days. Denise You mean like putting this in column B =IF(Abs((A1-Ave)/StdDev)>1,"This looks dodgy","") I have assumed that the data is in column A, you calculated the average of the data and put it in a cell named Ave, and you calculated the std dev and put it in a cell named StdDev. You can use the built-in formulae to calc average and std dev "Denise" <denise_blinn@hotmail.com> wrote in messag...

blind copy to self
I am looking for a setting in Outlook that allows me to send a blind copy to me whenever I send a message out. I want to make that a default so that a copy of my message is always available to me on server whenever I access my mail server on other computers. Currently all my outgoing messages are stored on "local" Sent Items folder that is not available to me on other computers. - Sam You can use an add-in for this; http://www.sperrysoftware.com/Outlook/Always-BCC.asp?Source=RS If you decide to order use "BH93RF24" to get a discount -- Robert Sparnaaij [MVP-Outlo...

copy paste #6
Hi, I have a copy paste code in one of my macro. I wanted to control the copy paste as only data. At present this is copying any blank cell or formula, that effect my sumproduct result as #value. below shown is the code : Range("A2:AH10000").Select Selection.Copy Range("A3").Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=True, _ IconFileName:=False So I wanted to be copy and paste only data in A2:AH10000 to be copied to down. Any idea will be helpful to me. thanx regards NOWFAL -- nowfal ---------------------------------------------------------...

Copying record value from column in multiple sheets into single sh
Hi, I have some employee Records in sheets "Nov07","Dec07" and "Jan08". the first two columns(A and B) are employee number and name in each sheet, and column C is their overtime. I want to create a consolidated sheet "YTD" so that I can see all their YTD overtime in one sheet and do some analysis. Hence the new sheet will have first two columns A and B same as above howver column C, D and E will be the overtime from each sheet. It would have been a simple copy paste if the number of employee had been static, but it changes every months as new em...

VB lookup date, then copy and paste
Hi All, I'm looking for some help on creating a macro for the following: Using cell A1 in Workbook 1 (contains a date in format dd/mm/yy), match this date with the same date in Column A in Workbook 2. Column A in Worksheet 2, starts with 31/01/10 in cell A2 and continues to 31/12/20 in cell A3989 (in date order). This end date may increase so I would like the macro to locate the end of column. When a match is found, I would like the macro to copy the content in Workbook 1 Row 1. This row starts at A1 and ends LJ, but again I would like this to be flexible. This data should be pasted ...

excel 2000 formula
can anyone help I'm new to excel formula, I want to find a function that will help me calculate the following: I'm a coach in athelitics, if my runners get below a certin time they go into a group "intermediat group" if they are above a certin time they go to the "advanced group" I need the formula to show nothing in the speadsheet and then when a time is entered it produces what group the athlete will be best suited to. Criteria: Less than 2 minutes = Advanced group More than 2 minutes = intermediate group and I need the cell to show nothing untill a time ...

How to reach at the source of the formula
HI IF I HAVE A FORMULA IN A1 CELL OF SHEET1 I.E "=SUM(SHEET2!B3:B40)", HOW CAN I GO TO THAT RANGE WITH OUT USING SHEET TAB WITH THE MOUSE OR alt+page down. AHMAD You can click "Trace precedent" on the Formula Auditing toolbar, then double-click on the dotted arrow line (leading from the little sheet icon) to surface the "Go To" dialog. Then just double-click on the range in the dialog's window and it'll zap you straight to that source range. If the above helped you in any/some way, pl press the YES button below p/s: Don't use all caps in your p...

repeating a formula throughout a worksheet
Hi I havent used excell for some years and have set up a basic worksheet, but am wanting to repeat a formula each third row which is row 1 balance plus row 2 minus row 3 equals balance as displayed in row 4 and then repeat this thoughtou the worksheet for several columns ( if that makes sense- im sure it can be done not sure how thou and have looked through help topics everywhere so if any one can help in basic language it would be great thanks. Jtnru If its single row then click your cell that contains the formula once then hold shift and click the further most cell that you want the f...

Allow VBA code to change a locked cell??
Hi, I am using Excel 2007. I have written some VBA code that sets the values in certain cells using code like the following: Worksheets("MySheet").Range("A5") = MyValue This code works when my worksheet is not protected and cell A5 is not locked. When I protect the worksheet (with A5 locked), my code does not work. I get run-time error '1004' - "The cell or chart that you are trying to change is protected and therefore read-only". I'd like to keep the cell locked (so users cannot edit it), but I do want my VBA code to be able...

Shadow Copies disabled on Reboot
I've tried this on several machines and see the same thing. I enable shadow copies on the c: drive. All seems to be working fine. It takes the initial copy and shows it in the list. If I reboot the server, the shadow copies for the c: drive are now set to disabled. The shadow copy I just made is still in the list, but that status is set to disabled. Has anyone else seen this? ...

formula to get true average
Hi, I need a formula to achieve a average for some mutual funds I have purchased over a period of years. Column A contains the number of shares I have purchased and Column C contain the price per share and Column D contains the cost for these shares. For example column A2 contains 130.29 and Column C2 contains $18.30 and Column D2 contains $2,484.25. Each cell in Column A contains a different number of shares and likewise so does column C and D. I have 40 cells in Column A each with a different number of shares and Column C has 50 cells each with a different amount per share and Colum...

Using text in a cell in a formula
I need to have a formula use the text of adjacent cells to determine where to look for values. I want to grab data from multiple datasheets but I don't want to have to type the sheet name in every formula, I want the formula to grab the name of the sheet from an adjacent cell. i.e. I need a formula like =if(sheet1!B2>0,100,""), but I need a function that will fill in the sheet name from the text value of an adjacent cell that would have the sheet name in it as I will be doing this for multiple cells on the same sheet. see your other post -- Biff Microsoft...

How do i make a variable part of a formula
Say I have some vba code that looks like: ActiveCell.Range("A1:B5").Select Selection.FormulaArray = _ "=LINEST(R[1]C[-3]:R[5]C[-3],R[1]C[-2]:R[5]C[2],TRUE,TRUE)" Let's say that I am looping over some variable i, and would someho like to insert that variable into the code: Selection.FormulaArray = _ "=LINEST(R[1]C[*i*]:R[5]C[*i+1 ],R[1]C[-2]:R[5]C[2],TRUE,TRUE)" Obviously, I can't simply insert the i's in there -- what instea should i do? thank -- Message posted from http://www.ExcelForum.com Selection.FormulaArray = _ "=LINEST(R[1]C["...

Array Formulas
Is there some kind of default I need to reset in order for the arra formulas with the {} bracket to take effect after pressin CTRL+SHIFT+ENTER? For some reason the formula is not bracketed {} eve after pressing the CTRL+SHIFT+ENTER keys. Thank you for any info you might be able to share -- Message posted from http://www.ExcelForum.com mymk You must be in Edit Mode when you CRTL + SHIFT + ENTER. Double-click on the cell or click on formula in formula bar. Then CSE Gord Dibben Excel MVP On Fri, 14 May 2004 16:34:36 -0500, mymk <<mymk.169mbn@excelforum-nospam.com>> wrote: &...

Cannot change criteria on copied chart without changing original c
I have a chart that I created from a pivot table. Instead of creating 40 plus charts individually, I tried to copy the original chart and change the location for each chart. When I change the facility location for one chart it changes all others. How can I get around this? Steve You'll need to make independent pivot tables for each chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services 774-275-0064 208-485-0691 fax jon@peltiertech.com http://PeltierTech.com/ _______ "Steve" <Steve@discussions.microsoft.com> wrote in message news:07FF39D...

cell formula
I have a cell formula i want to modify so it says "YES" if today is a monday and "No" if today is any other day of the week. =IF(B9="","YES","NO") at this stage it works if B9 is not empyy is this possible? thanks rslc Hi, How about this =IF(WEEKDAY(B9)=2,"Yes","No") -- Mike When competing hypotheses are...

Paste Formula that skips a cell.
I have a user that has received a file from a client that must not be altered apart from the required edits. The user has already discounted VLookup. The list from the client has two rows per "site". I need to paste a formula which looks to another file, this source file has only one row per "site". How can I paste the formula down the Client's site list but not have to edit the thousands of pasted formula to align the cells. eg. the paste into say client H4 is ok but client H6 should look to source H5, and client H5 will be blank, Client H8 will look to source H6....

Problem with PSTs created in Outlook 2000 when copied into Outlook 2002 SP2
Hi, I am facing a problem. There is a problem thats occuring with the PST which was created in Outlook 2000 when it is being configured for the profile copied into Outlook 2002 SP2. The default folders, Inbox/Outbox/Journal/Calendar are shown as junk. Could anyone has any clue as to what can be done? Thanks in advance. Param. How are you "configuring" the file? Have you checked it for errors? -- Russ Valentine [MVP-Outlook] "Param" <anonymous@discussions.microsoft.com> wrote in message news:1d40401c453b2$cd6eeee0$a501280a@phx.gbl... > Hi, > > I am fac...

Excel exact copy workbook to workbook HELP NEEDED !!
I have looked everywhere for this answer and I turn to the news groups to find the answer. I wish to copy a group of formulas from one excel workbook to another excel workbook. I could drag and drop but all the fomulas now reference the original workbook in their formulas. This is not what I wish to see. I need it to be an exact copy. I am dealing with way too much data to retype it all. Basically I want page six of the second workbook to look exactly like page six from the original workbook. John, Try this in Windows Explorer. Select the file. Menu pick edit/copy, then edit/ past...

Formula to work out someones age
I am putting together a database using excel and wish to enter a persons date of birth as a column and then have excel work out the persons age in the next column. Can anyone tell me the formula I should be using to do this? I'm using excel 97 on windows 95 Regards Mike music.bus@virgin.net --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/03 one way: =DATEDIF(A1, TODAY(),"y") where A1 contains the birthdate In article <HcI7b.277$eW.304297@newsfep...