Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formulas to certain sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formulas to certain sheets
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 "Jason Morin" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
How do I copy formulas but using the same range each time I copy | Excel Worksheet Functions | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |