Copy range of formulas across worksheets

0
votes

I have a range of formulas which need to be copied across all worksheets. The range goes from AB1:AC5. The formulas need to work across the worksheets and are taken from a worksheet called "Template" which is the first sheet in the workbook. I believe I have already selected the range and it will continue to only copy from "Template". How would I get it to paste onto every other sheet?

Sub FillSheets()
 Dim sh As Worksheet
 Dim rng As Range

 Dim worksheetsToSkip As Variant

 worksheetsToSkip = Array("Aggregated", "Collated Results", "Template", "End")
 Set rng = Sheet1.Range("AB1:AC5")

 For Each ws In Worksheets
    If IsError(Application.Match(ws.Name, worksheetsToSkip, 0)) Then


End Sub
2
excelvba

2 Answers

1
votes

you were quite near, since you had to use Formula property of Range object and a reference to the "source" range Address

also, you had a variable name mismatch: declared sh As Worksheet but then you used ws

try this:

Sub FillSheets()
    Dim sh As Worksheet
    Dim rng As Range

    Dim worksheetsToSkip As Variant

    worksheetsToSkip = Array("Aggregated", "Collated Results", "Template", "End")
    Set rng = Sheet1.Range("AB1:AC5")

    For Each sh In Worksheets
       If IsError(Application.Match(sh.Name, worksheetsToSkip, 0)) Then sh.Range(rng.Address).Formula = rng.Formula
    Next
End Sub
0
votes

The Exclamation Mark Issue

Cells Containing Sheet References

There is a worksheet named Template. You can write into cell A1 the formula =A2+A3. You can also write =Template!A2+A3 or =Template!A2+Template!A3. In all three cases the result is the same. But when you Paste Special the formulas into another sheet, you'll get different formulas in each case and therefore possibly different results. This code removes this possibility by pasting only the first scenario (=A2+A3).

When does Excel write these sheet references to a sheet?

In our example the case =Template!A2+A3 could have happened when the Template tab was selected, in the formula bar the equal sign (=) was entered, a different tab was clicked, then the Template tab was clicked, A2+A3 was entered and ENTER was pressed.
The case =Template!A2+Template!A3 could have happened when the Template tab was selected, in the formula bar the equal sign (=) was entered, a different tab was clicked, then the Template tab was clicked, A2 was selected, again a different tab was clicked, (+) was entered, again the Template tab was clicked, A3 was selected and ENTER was pressed.

Option Explicit

'*******************************************************************************
' Purpose:  Pastes formulas from a range in an initial worksheet               *
'           to the same range in all worksheets that are not included          *
'           in a specified worksheet-names list of exceptions.                 *
' Remarks:  There has to be a worksheet with the codename "Sheet1"             *
'           in the workbook in which this code resides or it will not compile. *
'*******************************************************************************
Sub FillSheetsWithRangeOfFormulas()

  Const cStrRange = "AB1:AC5"                         ' Initial Range Address
  Const cStrSkip = "Aggregated,Collated Results,End"  ' List of Exceptions
  Const cStrSkipSeparator = ","                       ' List of Exceptions Sep.

  Dim objWs As Worksheet      ' Worksheet Object to be Used in a For Each Loop
  Dim vntSkip As Variant      ' List of Exceptions Array
  Dim vntFormulas As Variant  ' Formulas Array
  Dim lngRows As Long         ' Formulas Array Rows Counter
  Dim intColumns As Integer   ' Formulas Array Columns Counter
  Dim vntWb As Variant        ' Workbooks Array
  Dim intWb As Integer        ' Workbooks Array Rows Counter
  Dim strDel As String        ' Worksheet Reference String ("!" & Sheet1.Name)
  Dim strWb As String         ' Workbooks Array Split String ("]" & strDel)
  Dim strWbTemp As String     ' Workbooks Array Temporary String ("" or strWb)
  Dim strWbResult As String   ' Workbooks Array Resulting String

  With Sheet1
    ' Paste Initial-Range formulas into (1-based 2-dimensional) Formulas Array.
    vntFormulas = .Range(cStrRange).Formula
    ' Define Worksheet Decalaration String
    strDel = .Name & "!"
  End With

  ' Define Workbooks Array Split String to use to not remove worksheet
  ' references to sheets with the same name as Sheet1 in other workbooks.
  strWb = "]" & strDel

  ' Remove worksheet(!) references from formulas in Formulas Array.

  ' In the following For-Next loop, in the comments, "Template" for Sheet1's
  ' name is used.

  For intColumns = LBound(vntFormulas, 2) To UBound(vntFormulas, 2)
    For lngRows = LBound(vntFormulas) To UBound(vntFormulas)

      ' Check if element does not contain "]Template!" which would indicate that
      ' it is linking to a sheet with the same name in another workbook.
      If InStr(1, vntFormulas(lngRows, intColumns), strWb, _
          vbTextCompare) = 0 Then   ' Does NOT contain "]Template!" (strWb).

        ' Check if element contains just "Template!" (strDel).
        If InStr(1, vntFormulas(lngRows, intColumns), strDel, _
            vbTextCompare) <> 0 Then    ' DOES contain "Template!" (strDel).
          ' Write resulting string to Formulas Array (overwriting).
          vntFormulas(lngRows, intColumns) = Replace(vntFormulas(lngRows, _
              intColumns), strDel, "", , , vbTextCompare)
'         Else                          ' Does NOT contain "Template!" (strDel).
        End If

       Else                         ' DOES contain "]Template!" (strWb).

        strWbResult = ""
        ' Split the element's string by "]Template!" (strWb) into a 0-based
        ' 1-dimensional array.
        vntWb = Split(vntFormulas(lngRows, intColumns), strWb, , vbTextCompare)
        ' Rebuild the string removing additional "Template!" (strDel) strings.
        For intWb = LBound(vntWb) To UBound(vntWb)
          If intWb <> 0 Then ' Is NOT first element of array.
            strWbTemp = strWb
           Else              ' IS first element of array.
            strWbTemp = ""
          End If
          ' Check if element contains just "Template!" (strDel).
          If InStr(1, vntWb(intWb), strDel, _
              vbTextCompare) <> 0 Then  ' DOES contain "Template!" (strDel).
            strWbResult = strWbResult & strWbTemp & Replace(vntWb(intWb), _
                strDel, "", , , vbTextCompare)
           Else                         ' Does NOT contain "Template!" (strDel).
            strWbResult = strWbResult & strWbTemp & vntWb(intWb)
          End If
        Next
        Erase vntWb
        ' Write resulting string to Formulas Array (overwriting).
        vntFormulas(lngRows, intColumns) = strWbResult

      End If

    Next
  Next

  With Sheet1
    ' Populate (0-based 1 dimensional) List of Exceptions Array (vntSkip),
    ' after adding Sheet1's name (.Name i.e. Sheet1.Name).
    vntSkip = Split(cStrSkip & cStrSkipSeparator & .Name, cStrSkipSeparator)
    ' Paste Formulas Array (vntFormulas) into the range (same size and position
    ' as the Initial Range (cStrRange)) of each worksheet whose name is not
    ' contained in the List of Exceptions (vntSkip) in the workbook
    ' (.Parent.Name i.e. Sheet1.Parent.Name) where Sheet1 resides.
    For Each objWs In Workbooks(.Parent.Name).Worksheets
      If IsError(Application.Match(objWs.Name, vntSkip, 0)) Then _
          objWs.Range(cStrRange).Formula = vntFormulas
    Next
  End With

  Erase vntSkip
  Erase vntFormulas

End Sub
'*******************************************************************************