Copy formulas to certain sheets

  • Thread starter Thread starter Jason Morin
  • Start date Start date
J

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
 
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
 
Back
Top