Need help with some "Private Sub WorkSheet_Change" code - Programmers Heaven

Howdy, Stranger!

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

Categories

Need help with some "Private Sub WorkSheet_Change" code

DOC298DOC298 Posts: 2Member
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
Sheets("Type1").Select
ActiveSheet.Unprotect
Rows("2:2").Select
Range("A2").Activate
Selection.Copy
Rows("5:5").Select
Range("C5").Select
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
Sheets("sheet1").Select
Range("F8").Select
Else
.............

End Sub


Sign In or Register to comment.