Copying and Pasting Different Formulas

[Guest]

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 A11 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 A11 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 A11, 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

 

[C01d]

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)

 

[Guest]

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