copy and paste formula without clipboard?

[samsilverman]

Is there a way to copy and paste a formula with some vba instead of the clipboard? Every once and awhile, I'll be trying to make duplicate copies of a page in a worksheet so that I have 50 - 100 pages of the same template. Sometimes the formulas copy/paste great with relative referencing. But sometimes, it will paste like it's an absolute reference. Plus, the copy/paste is reallllllly slow sometimes with a lot of formulas.

Any help or ideas?

 

[VoG]

You can write a formula directly to a range, for example

Range("C1:C100").Formula = "=A1+B1"

 

[samsilverman]

OK VoG, you've been VERY helpful (directly and indirectly) in the past and I thank you very much for the reply, but...

what does that do exactly?

Plus, why would this formula paste as an absolute reference?

=IF(ISBLANK(B11),"",IF(OR(K12="*",K15="*",K16="*",K23="*",K26="*",K31="*",K34="*",C30="*"),"* - NOTE HIGHLIGHTED ITEMS ABOVE.","ALL AREAS MEET OR EXCEED MINIMUM STANDARDS."))

 

[VoG]

It is a trivial example. in C1 it puts

=A1+B1

in C2

=A2+B2

and so on.

 

[samsilverman]

oh, ok.
Why would this formula paste as an absolute reference?

=IF(ISBLANK(B11),"",IF(OR(K12="*",K15="*",K16="*",K23="*",K26="*",K31="*",K34="*",C30="*"),"* - NOTE HIGHLIGHTED ITEMS ABOVE.","ALL AREAS MEET OR EXCEED MINIMUM STANDARDS."))

 

[VoG]

I don't know. It doesn't look like it should.

 

[samsilverman]

Bummer. Was hoping something about it would be obvious to you. Thanks for your help!

 

[GTO]

Hi there,

You are already getting better help than what I'd likely be, and this isn't well thought through... but as I'm signing out and this looked like it might work,,,

Option Explicit
    
Sub exa()
Dim rng As Range, Cell As Range
Dim aryFormulas, aryVals
Dim x As Long, y As Long
    
    Set rng = Range("A1:J10")
    
    aryFormulas = rng.FormulaR1C1
    aryVals = rng.Value
    
    For x = LBound(aryFormulas, 1) To UBound(aryFormulas, 1)
        For y = LBound(aryFormulas, 2) To UBound(aryFormulas, 2)
            
            If Not Left(aryFormulas(x, y), 1) = "=" Then
                aryFormulas(x, y) = aryVals(x, y)
            End If
        Next
    Next
    
    Sheet2.Range("C3").Resize(UBound(aryFormulas, 1), UBound(aryFormulas, 2)).Value = aryFormulas
End Sub

 

[samsilverman]

Ummmm....can anyone tell me what that does? (thanks btw, GTO)

 

[GTO]

My thought (wacky or not) was to plunk the range into two arrays. One made up of ea cell's "formula" and the other of the values. Then run through the array of "formulas" replacing any elements that are not actually formulas with the corresponding element from the array of values. I was thinking that this should keep the references relative.

Did it work?