Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Need help with some "Private Sub WorkSheet_Change" code

DOC298DOC298 Member Posts: 2
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.Insert Shift:=xlDown
Selection.EntireRow.Hidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Application.CutCopyMode = False
Application.EnableEvents = True

End Sub

Sign In or Register to comment.