Need help with VBA code. Tons of code seperated in two, second part of code doesn't work.

I had to break up the 2 sections of code because it was to long.
The left column on a sheet will run the first part of the code.
And the right column on that same sheet will run the second set.
I'm not sure what the second part should be at the very beginning.

You'll notice a line across the whole document where the second part of the code starts.
I've tried changing the name of the second part of the code, but it doesn't work.
If I delete the first part of the code, then the second one works fine.

It seems to me your first 750 lines could be reduced to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Rw As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
    '==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
    blHide = (Target.Value = ""): Rw = Target.Row
    For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
        With ws
            .Unprotect (sPW)
            .Range(Rw & ":" & Rw & "," & Rw + 41 & ":" & Rw + 41 & "," & _
              Rw + 82 & ":" & Rw + 82 & "," & Rw + 123 & ":" & Rw + 123).EntireRow.Hidden = blHide
            .Protect (sPW)
        End With
    Next
    With Sheets("Extra Week")
        .Unprotect (sPW)
        .Range(Rw & ":" & Rw).EntireRow.Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("Cosmetician Sales")
        .Unprotect (sPW)
        .Range(Rw & ":" & Rw & "," & Rw + 31 & ":" & Rw + 31).EntireRow.Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("CAST")
        .Unprotect (sPW)
        .Range((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).EntireRow.Hidden = blHide
        .Protect (sPW)
    End With
End If
Application.ScreenUpdating = True
End Sub

I'll leave it to you to simplify the rest of your code and incorporate it into the above sub.

I'm not sure what all that code means (.Range(Rw & ":" & Rw & "," & Rw + 41 & ":" & Rw + 41 & "," & _
Rw + 82 & ":" & Rw + 82 & "," & Rw + 123 & ":" & Rw + 123)), but looks very interesting, and I guess saves a ton of editing.

Thanks, and I'll look at it soon.

O.K. I think I figured it out.
So basically if cell E4 is empty, then look at row 4, then the 41'st row after that, etc.
And I guess columns would be something like:

If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
blHide = (Target.Value = ""): Cw = Target.Column
For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
With ws
.Unprotect (sPW)
.Range(Cw & ":" & Cw & "," & Cw + 41 & ":" & Cw + 41 & "," & _
Cw + 82 & ":" & Cw + 82 & "," & Cw + 123 & ":" & Cw + 123).EntireColumn.Hidden = blHide
.Protect (sPW)
End With

Originally Posted by FragaGeddon

I'm not sure what all that code means

Have a look at what Rw is and how it is derived. The rest is just math & string compilation.

Originally Posted by FragaGeddon

And I guess columns would be something like:

If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
blHide = (Target.Value = ""): Cw = Target.Column
For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
With ws
.Unprotect (sPW)
.Range(Cw & ":" & Cw & "," & Cw + 41 & ":" & Cw + 41 & "," & _
Cw + 82 & ":" & Cw + 82 & "," & Cw + 123 & ":" & Cw + 123).EntireColumn.Hidden = blHide
.Protect (sPW)
End With

Correct, though you should also define Cw As Long (Since Rw is an abbreviation of Row, I wouldn't abbreviate Column as Cw).

PS: Please use the code tags, inserted via the # symbol on the posting menu, when posting code.

Not sure if I'm doing something wrong, but I did the second part of the code to hide columns as a test. If I delete a cell from I4, it hides all columns in that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_Change_B Target
Worksheet_Change_V Target
End Sub
Private Sub Worksheet_Change_B(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Rw As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
    '==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
    blHide = (Target.Value = ""): Rw = Target.Row
    For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
        With ws
            .Unprotect (sPW)
            .Range(Rw & ":" & Rw & "," & Rw + 41 & ":" & Rw + 41 & "," & _
              Rw + 82 & ":" & Rw + 82 & "," & Rw + 123 & ":" & Rw + 123).EntireRow.Hidden = blHide
            .Protect (sPW)
            .EnableSelection = xlUnlockedCells
        End With
    Next
    With Sheets("Extra Week")
        .Unprotect (sPW)
        .Range(Rw & ":" & Rw).EntireRow.Hidden = blHide
        .Protect (sPW)
        .EnableSelection = xlUnlockedCells
    End With
    With Sheets("Cosmetician Sales")
        .Unprotect (sPW)
        .Range(Rw & ":" & Rw & "," & Rw + 31 & ":" & Rw + 31).EntireRow.Hidden = blHide
        .Protect (sPW)
        .EnableSelection = xlUnlockedCells
    End With
    With Sheets("CAST")
        .Unprotect (sPW)
        .Range((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).EntireRow.Hidden = blHide
        .Protect (sPW)
        .EnableSelection = xlUnlockedCells
    End With
End If
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change_V(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Cl As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
    '==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("I4:I33")) Is Nothing Then
    blHide = (Target.Value = ""): Cl = Target.Column
    For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
        With ws
            .Unprotect (sPW)
            .Range(Cl & ":" & Cl).EntireColumn.Hidden = blHide
            .Protect (sPW)
            .EnableSelection = xlUnlockedCells
        End With
    Next
    With Sheets("Extra Week")
        .Unprotect (sPW)
        .Range(Cl & ":" & Cl).EntireColumn.Hidden = blHide
        .Protect (sPW)
        .EnableSelection = xlUnlockedCells
    End With
End If
Application.ScreenUpdating = True
End Sub

Originally Posted by FragaGeddon

Not sure if I'm doing something wrong, but I did the second part of the code to hide columns as a test. If I delete a cell from I4, it hides all columns in that sheet.

What are you trying to hide/unhide? Yoy have defined 'Cl = Target.Column', but that just returns a number thatou need to convert that to a letter. But, if you do that and reference Cl for 'Hidden = blHide', what you'll end up hiding is column I. Is that your intention?

Is it only I4 you're concerned with for the column hiding/unhiding? Your code presently tests I4:I33, so any of those cells will trigger that.

So why is your added code trying to hide columns on sheets "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13" & "Extra Week", but not on the sheet "Daily Sales Tracking"?

Sorry I was just testing to see if it worked. For the "Daily Sales Tracking" sheet, this is what needs to be done:

On the "Input Info" sheet, cells I4 to I33 it would have a name or have nothing, basically the same as E4 to E33.
Except I need it to hide 2 rows & 2 columns at the same time on the "Daily Tracking Sheet". There's already 4 columns hidden between each of the 2 columns that need to be displayed, or hidden.

 
 I4 = Column J:K & Row 382:383
 I5 = Column P:Q & Row 384:385
 I6 = Column V:W & Row 386:387
 I7 = Column AB:AC & Row 388:389
 I8 = Column AH:AI & Row 390:391
 I9 = Column AN:AO & Row 392:393
I10 = Column AT:BU & Row 394:395
I11 = Column AZ:BA & Row 396:397
I12 = Column BF:BG & Row 398:399
I13 = Column BL:BM & Row 400:401

I14 = Column BR:BS & Row 402:403
I15 = Column BX:BY & Row 404:405
I16 = Column CD:CE & Row 406:407
I17 = Column CJ:CK & Row 408:409
I18 = Column CP:CQ & Row 410:411
I19 = Column CV:CW & Row 412:413
I20 = Column DB:DC & Row 414:415
I21 = Column DH:DI & Row 416:417
I22 = Column DN:DO & Row 418:419
I23 = Column DT:DU & Row 420:421

I24 = Column DZ:DA & Row 422:423
I25 = Column EF:EG & Row 424:425
I26 = Column EL:EM & Row 426:427
I27 = Column ER:ES & Row 428:429
I28 = Column EX:EY & Row 430:431
I29 = Column FD:FE & Row 432:433
I30 = Column FJ:FK & Row 434:435
I31 = Column FP:FQ & Row 436:437
I32 = Column FV:FW & Row 438:439
I33 = Column GB:GC & Row 440:441

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Rw As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
    '==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
    blHide = (Target.Value = ""): Rw = Target.Row
    For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
        With ws
            .Unprotect (sPW)
            .Rows(Rw).Hidden = blHide
            .Rows(Rw + 41).Hidden = blHide
            .Rows(Rw + 82).Hidden = blHide
            .Rows(Rw + 123).Hidden = blHide
            .Protect (sPW)
        End With
    Next
    With Sheets("Extra Week")
        .Unprotect (sPW)
        .Rows(Rw).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("Cosmetician Sales")
        .Unprotect (sPW)
        .Rows(Rw).Hidden = blHide
        .Rows(Rw + 31).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("CAST")
        .Unprotect (sPW)
        .Rows((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("Daily Sales Tracking")
        .Unprotect (sPW)
        .Rows((Rw - 4) * 2 + 382 & ":" & (Rw - 4) * 2 + 383).Hidden = blHide
        .Columns((Rw - 3) * 6 + 4).Hidden = blHide
        .Columns((Rw - 3) * 6 + 5).Hidden = blHide
        .Protect (sPW)
    End With
End If
Application.ScreenUpdating = True
End Sub

Note that I've revised some of the earlier code to make it easier for people to understand and, hence, maintain.

Originally Posted by FragaGeddon

The only thing I get confused about is when you do for the "CAST" sheet: .Rows((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19)
I'm guessing your saying start at row 0 (Rw - 4), then hide the next 19 rows, then add 1 blank row(?), and the second part would start at row 1 and hide the next 19 rows?

If Rw = 4 then (Rw - 4) = 0. 0 *19 = 0 and 0 +1 = 1. Similarly Rw = 4 then (Rw - 3) = 1. 1 *19 = 1. 1 & ":" & 19 = 1:19. So .Rows(1:19).Hidden = blHide
If Rw = 5 then (Rw - 4) = 1. 1 *19 = 1 and 19 +1 = 20. Similarly Rw = 5 then (Rw - 3) = 2. 2 *19 = 38. 20 & ":" & 38 = 20:38. So .Rows(20:38).Hidden = blHide
etc.
Rows are never added to the workbook.

Awesome. I totally understand now. Thanks again for all your help.

Edit: I kind of thought it was a math equation. I was thinking why would you add + 1 to zero, but it really makes sense when you get to the next box in I5, and so on.

Since the outcomes for columns E & I are the same for all rows, and the only column-based difference concerns updating the "Daily Sales Tracking" worksheet if column I is being updated, your entire 1356 lines of code could be reduced to just 45 lines:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Rw As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
    '==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Or Not Intersect(Target, ActiveSheet.Range("I4:I33")) Is Nothing Then
    blHide = (Target.Value = ""): Rw = Target.Row
    For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
        With ws
            .Unprotect (sPW)
            .Rows(Rw).Hidden = blHide
            .Rows(Rw + 41).Hidden = blHide
            .Rows(Rw + 82).Hidden = blHide
            .Rows(Rw + 123).Hidden = blHide
            .Protect (sPW)
        End With
    Next
    With Sheets("Extra Week")
        .Unprotect (sPW)
        .Rows(Rw).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("Cosmetician Sales")
        .Unprotect (sPW)
        .Rows(Rw).Hidden = blHide
        .Rows(Rw + 31).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("CAST")
        .Unprotect (sPW)
        .Rows((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).Hidden = blHide
        .Protect (sPW)
    End With
End If
If Not Intersect(Target, ActiveSheet.Range("I4:I33")) Is Nothing Then
    With Sheets("Daily Sales Tracking")
        .Unprotect (sPW)
        .Rows((Rw - 4) * 2 + 382 & ":" & (Rw - 4) * 2 + 383).Hidden = blHide
        .Columns((Rw - 3) * 6 + 4).Hidden = blHide
        .Columns((Rw - 3) * 6 + 5).Hidden = blHide
        .Protect (sPW)
    End With
End If
Application.ScreenUpdating = True
End Sub

OK, so with 52 lines of code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Rw As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
    '==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
    blHide = (Target.Value = ""): Rw = Target.Row
    For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
        With ws
            .Unprotect (sPW)
            .Rows(Rw).Hidden = blHide
            .Rows(Rw + 41).Hidden = blHide
            .Rows(Rw + 82).Hidden = blHide
            .Rows(Rw + 123).Hidden = blHide
            .Protect (sPW)
        End With
    Next
    With Sheets("Extra Week")
        .Unprotect (sPW)
        .Rows(Rw).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("Cosmetician Sales")
        .Unprotect (sPW)
        .Rows(Rw).Hidden = blHide
        .Rows(Rw + 31).Hidden = blHide
        .Protect (sPW)
    End With
    With Sheets("CAST")
        .Unprotect (sPW)
        .Rows((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).Hidden = blHide
        .Protect (sPW)
    End With
End If
If Not Intersect(Target, ActiveSheet.Range("I4:I33")) Is Nothing Then
    With Sheets("Vendor Sales")
            .Unprotect (sPW)
            .Rows(Rw).Hidden = blHide
            .Rows(Rw + 34).Hidden = blHide
            .Rows(Rw + 68).Hidden = blHide
            .Protect (sPW)
    End With
    With Sheets("Daily Sales Tracking")
        .Unprotect (sPW)
        .Rows((Rw - 4) * 2 + 382 & ":" & (Rw - 4) * 2 + 383).Hidden = blHide
        .Columns((Rw - 3) * 6 + 4).Hidden = blHide
        .Columns((Rw - 3) * 6 + 5).Hidden = blHide
        .Protect (sPW)
    End With
End If
Application.ScreenUpdating = True
End Sub