How to Create a Loop to Copy and Paste Formulas From a Template Sheet to Multiple Other Sheets?

EXWIP

New Member
Joined
Jan 19, 2016
Messages
6
Model Components
1 [MASTER] sheet. This sheet stores all of the formulas that are used to overwrite the other sheets.
100 asset sheets. These worksheets are exactly the same as the master sheet except that the end users replace some of the formulas with hard coded values.

Problem:

At the end of every month,
  • Look at cells C7 thru N7; if “Actual” reset all formulas in rows 21 thru 233 in corresponding columns
  • Look at cells C7 thru N7; if “Actual”, lock rows 22 thru 233 in corresponding columns
Asset sheets and master sheet both set up the exact same way:
CDEFGHIJKL
71/31/20202/29/20203/31/20204/30/20205/31/20206/30/20207/31/20208/31/20209/30/202010/31/2020
8ActualActualForecastForecastForecastForecastForecastForecastForecastForecast

Is there a simple way to accomplish this task with VBA?

Here is the code that I'm using to loop through the asset worksheets.

NonPropSheets is the list of non-asset tabs that shouldn't be overwritten.

VBA Code:
Sub ResetActuals()

    Dim strExcludeSheets() As String
    Dim lngArrayIndex As Long
    Dim rngMyCell As Range
    Dim ws As Worksheet
    Dim intResult As Integer
    
    Application.ScreenUpdating = False
    
    'Build an array of sheet(s) to be excluded
    For Each rngMyCell In ActiveWorkbook.Names("NonPropSheets").RefersToRange
        If Len(rngMyCell) > 0 Then
            ReDim Preserve strExcludeSheets(lngArrayIndex)
            strExcludeSheets(lngArrayIndex) = rngMyCell.Value
            lngArrayIndex = lngArrayIndex + 1
        End If
    Next rngMyCell
    
    For Each ws In ActiveWorkbook.Sheets
        'If the sheet name is not in the 'strExcludeSheets' array, then...
        If Not IsNumeric(Application.Match(ws.Name, strExcludeSheets, 0)) Then
        
        'insert restore template code here
                      
        
        End If
    Next ws
    
    Application.ScreenUpdating = True

End Sub

Credit to Trebor76 for the above VBA.

Any help or guidance is greatly appreciated.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

EXWIP

New Member
Joined
Jan 19, 2016
Messages
6
Forgot to mention, my current manual process is to copy the "Actual" month formulas from the MASTER sheet and paste them onto each of the property sheets. In this case, that would mean copying C21:D233 from the MASTER sheet and pasting it to C21:D233 on all the other sheets. Then going back and locking those same ranges on the property sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,149
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top