Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Different Formulas
Greetings!
I have a workbook several sheets, say Sheet 1-10. The first sheet, say Sheet1, contains 4 different formulas that I need to paste into the other sheets. The formulas are contained in cells A1:D1 so that each cell contains a different formula. The issue is that I dont need all the formulas in all sheets. For some of the sheets I only need one of the formulas, for some I need two of them, and for others I need three of them. Therefore, I cant just copy cells A1:D1 as a group and paste into the other sheets. I'm trying to have my macro store all the different formulas, so that when each of the sheets 2-10 is activated I can paste formulas that apply to that sheet. The code that I came up with is (to make it shorter, I only show the part that applies to sheet2): dim rng1 as range, rng2 as range, rng3 as range, rng4 as range dim rng5 as range, rng6 as range set rng1 = Worksheets("Sheet1").Range("A1") set rng2 = Worksheets("Sheet1").Range("B1") set rng3 = Worksheets("Sheet1").Range("C1") set rng4 = Worksheets("Sheet1").Range("D1") fm1 = rng1.formulas fm2 = rng2.formulas fm3 = rng3.formulas fm4 = rng4.formulas Set rng5 = Worksheets("Sheet2").Range("E1") Set rng6 = Worksheets("Sheet2").Range("F1"). rng5=fm1 rng6=fm3 The problem with this is that the formulas are absolute. The macro uses the same exact formulas contained in cells A1:D1, without adjusting to the position of the destination cells (Sheet2!E1:F1 in this case). Is it possible to have it the macro adjust for the position of the destination cells? I would greatly appreciate any insight that you provide on this! Sincerely, Magnivy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Different Formulas
The Range class has two functions that you could try using, namely, copy and pastespecial. Your code would look similar to this: rng1.Copy rng5.PasteSpecial(xlPasteFormulas) -- C01d ------------------------------------------------------------------------ C01d's Profile: http://www.excelforum.com/member.php...o&userid=34422 View this thread: http://www.excelforum.com/showthread...hreadid=548157 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Different Formulas
C01d, thanks for responding. If I use the copy function, I will have to have
the macro go back and forth to Sheet 1. For example, I would have to have it copy the first formula, paste it in Sheet2, go back to Sheet1, copy the second formula and paste it in Sheet2. I'm trying to come up with a way which would store all the formulas, so that it would not have to go back to Sheet1. Would you happen to know of a way to do that? Thanks a lot for your help! Magnivy "C01d" wrote: The Range class has two functions that you could try using, namely, copy and pastespecial. Your code would look similar to this: rng1.Copy rng5.PasteSpecial(xlPasteFormulas) -- C01d ------------------------------------------------------------------------ C01d's Profile: http://www.excelforum.com/member.php...o&userid=34422 View this thread: http://www.excelforum.com/showthread...hreadid=548157 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TROUBLE COPYING AND PASTING FORMULAS | Excel Worksheet Functions | |||
Copying/Pasting Formulas | Excel Discussion (Misc queries) | |||
Copying and pasting multiple formulas, it it possible? | Excel Discussion (Misc queries) | |||
Copying & pasting formulas | Excel Worksheet Functions | |||
Pasting formulas without copying file ref. | Excel Programming |