I am trying to write some code to check a field for text and depending on it's results copy that information into 1 of 4 spreadsheets (depending on the results - Type1, Type2, Type3 & Type4). All Sheets are protected when opening and I know that the copying and inserting copied rows on its own works.
The problem I have is that this does not seem to update the other sheets when I enter the Type into Cell F8 of the AllTypes Worksheet (There are only 4 options and so I have data validation via a list set on this field if that makes a difference). On the "AllTypes" worksheet there is an "Add Information" button, which creates a new line at F8 with all the Formulas and formatting setup ready for simple data entry. Do I need a loop and if so what is the best way of doing this.
Thanks in advance
I have not do ANY coding for a long time so I imagine this can easily be improved upon, so any help welcome.
This is the code I presently have
Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("AllTypes").Range("F8") = "Type1" Then
Application.EnableEvents = False
Selection.EntireRow.Hidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.CutCopyMode = False
Application.EnableEvents = True