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,
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.
Credit to Trebor76 for the above VBA.
Any help or guidance is greatly appreciated.
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
C | D | E | F | G | H | I | J | K | L |
7 | 1/31/2020 | 2/29/2020 | 3/31/2020 | 4/30/2020 | 5/31/2020 | 6/30/2020 | 7/31/2020 | 8/31/2020 | 9/30/2020 | 10/31/2020 |
8 | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast |
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.