Copy formulas to certain sheets

[Jason Morin]

My problem: I need to copy certain formulas from a
worksheet sheet called "SERDC Cover" to the same cells in
several other worksheets. However, I only want to:

1) Copy only those cells whose formulas start
with "=Input!"
2) Only copy to those worksheets whose name
contains "Cover".

For example, A1 on "SERDC Cover" has "=Input!B22". I would
like to put "=Input!B22" in cell A1 for all other
worksheets in the wb that contains the "Cover" in the name.

Thanks!
Jason

 

[Tom Ogilvy]

Sub tester4()
Dim rng As Range, rng1 As Range, cell As Range
Dim sStr As String, sName As String
Dim sh As Worksheet
On Error Resume Next
Set rng = Worksheets("SERDC Cover").Cells.SpecialCells(xlFormulas)
On Error goto 0
if rng is nothing then exit sub
Set rng1 = Nothing
For Each cell In rng
sStr = UCase(cell.Formula)
If InStr(sStr, "INPUT") Then
If Not rng1 Is Nothing Then
Set rng1 = Union(cell, rng1)
Else
Set rng1 = cell
End If
End If
Next
If Not rng1 Is Nothing Then
For Each sh In ThisWorkbook.Worksheets
sName = UCase(sh.Name)
If InStr(sName, "COVER") And _
sh.Name <> rng.Parent.Name Then
For Each cell In rng1
sh.Range(cell.Address).Formula = cell.Formula
Next
End If
Next
End If
End Sub


Compiled but not executed - but should give you a start

Regards,
Tom Ogilvy