Hi Gurus,
I have a macro (courtesy of the wonderful forum) within a workbook that is used once a month to clear out all the data from specific cells so we can set up a new empty workbook for the next month.
The workbook contains several macros that users can and will use on a daily basis. Obviously I don't want any one running that macro accidentally during the month and, ideally, I would prefer the macro to only be run after entering a password (that only I will know). At the moment I am manually creating this macro at the time I want to do the clear down but if I can protect the macro I can keep it in the workbook.
Q1: Is that possible and how can I achieve this?
My cleardown macro deletes content from cells A2:C2000 in multiple sheets, with some excluded. However in column D2 to D2000 I have data validation cells with values set from the previous month. I would like to reset all D2:D2000 cells to the first in the list. The list is from another sheet called "Outcomes" and looks like this ='OUTCOMES'!$A$2:$A$12
Q2: If this is possible can I add this feature to the VBA below?
I have a macro (courtesy of the wonderful forum) within a workbook that is used once a month to clear out all the data from specific cells so we can set up a new empty workbook for the next month.
The workbook contains several macros that users can and will use on a daily basis. Obviously I don't want any one running that macro accidentally during the month and, ideally, I would prefer the macro to only be run after entering a password (that only I will know). At the moment I am manually creating this macro at the time I want to do the clear down but if I can protect the macro I can keep it in the workbook.
Q1: Is that possible and how can I achieve this?
My cleardown macro deletes content from cells A2:C2000 in multiple sheets, with some excluded. However in column D2 to D2000 I have data validation cells with values set from the previous month. I would like to reset all D2:D2000 cells to the first in the list. The list is from another sheet called "Outcomes" and looks like this ='OUTCOMES'!$A$2:$A$12
Q2: If this is possible can I add this feature to the VBA below?
VBA Code:
Sub Clear_Range()
'Clear off all data from sheets 01-31
Application.ScreenUpdating = False
Dim ans As Long
ans = ThisWorkbook.Sheets.Count
Dim i As Long
For i = 1 To ans
If Sheets(i).Name <> "OUTCOMES" And Sheets(i).Name <> "Raw Data" And Sheets(i).Name <> "Action" And Sheets(i).Name <> "With List" Then Sheets(i).Range("A2: C2000").ClearContents
Next
Application.ScreenUpdating = True
End Sub