This is going to be hard to explain so I hope I can explain it good enough.
We have a workbook with many sheets that are dependent on each other. A problem is occurring when I import data from a file and it gets mapped to the appropriate row on one of the sheets.
We start on (Sheet27) and run the update it goes and looks at the import sheet data grabs the data and then puts that onto (sheet7)

I think the problem is The range the data gets put into is hidden until the next batch is imported then it unhides the next row ready for import.

I want to be able to run the import from Sheet27 and not have to go and view Sheet7 to trigger its code on its change event.

How can I get it to run the code and update the other sheet and have that sheet still update its coding properly in the background without having to look at it every time. I tell it to activate the sheet in the code thinking it recognizes the change event and then runs it code in the background but it is not doing that.


Sub UpdateSurvey()
    Dim wBook As Workbook, path As String, maxRow As Long, wBookSvy As Worksheet, Surveys As Worksheet
    Dim col As Long, row As Long
    
    'get integers for the cell reference in the "Folder Path Cell" on the template sheet
    col = wColNumber(colRegEx(Range("CU43").Text))
    row = CInt(rowRegEx(Range("CU43").Text))
    
    'Turns off screen updates to avoid flashing screen
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
        
    'saving the survey sheet object to a variable
    Set Surveys = ThisWorkbook.Sheets("Survey Report")
    
    'setting the defined path for the survey file
    path = cells(row, col).Text
    If Right(path, 1) <> "\" Then path = path + "\"
    
    'Find the file marked as the survey file then exit loop
    For i = row + 2 To row + 15
        'If cells(i, col + 11).Value = "Yes" Then
        If LCase(cells(i, col + 8).Value) = "yes" Then
            path = path + cells(i, col + 2).Text
            Exit For
        End If
    Next i
    
   'Open the generated survey file under the object stored by wBook, set the page object in the open book
    Set wBook = Application.Workbooks.Open(path)
    Set wBookSvy = wBook.Sheets(1)
    
    'Find the last row in the generated survey file, then navigate to the daily sheet survey and copy MD/Inc/Azm,Temp,Source
      maxRow = Range("B" & Rows.Count).End(xlUp).row

    Surveys.Activate
    Surveys.Unprotect "LDD3006!"
    
    
    'Surveys.Range("A17:B367").ClearContents
    Surveys.Range("D17:F367").ClearContents
    Surveys.Range("X17:X367").ClearContents
    Surveys.Range("Y17:Y367").ClearContents
    Surveys.Range("Z17:Z367").ClearContents
    Surveys.Range("AA17:AA367").ClearContents
    Surveys.Range("AB17:AB367").ClearContents
    Surveys.Range("AC17:AC367").ClearContents
    Surveys.Range("AD17:AD367").ClearContents
    Surveys.Range("AE17:AE367").ClearContents
    Surveys.Range("AF17:AF367").ClearContents
    Surveys.Range("AG17:AG367").ClearContents
    Surveys.Range("AH17:AH367").ClearContents
    
    
    'JOBBOOK----------------------------EXPORT.XLS
    Surveys.Range("A17:A" & maxRow + 14) = wBookSvy.Range("A3:A" & maxRow).Value
    Surveys.Range("D17:F" & maxRow + 14) = wBookSvy.Range("B3:D" & maxRow).Value
    Surveys.Range("X17:X" & maxRow + 14) = wBookSvy.Range("E3:E" & maxRow).Value
    Surveys.Range("Y17:Y" & maxRow + 14) = wBookSvy.Range("G3:G" & maxRow).Value
    Surveys.Range("Z17:Z" & maxRow + 14) = wBookSvy.Range("I3:I" & maxRow).Value
    Surveys.Range("AA17:AA" & maxRow + 14) = wBookSvy.Range("M3:M" & maxRow).Value
    Surveys.Range("AB17:AB" & maxRow + 14) = wBookSvy.Range("K3:K" & maxRow).Value
    Surveys.Range("AC17:AC" & maxRow + 14) = wBookSvy.Range("P3:P" & maxRow).Value
    Surveys.Range("AD17:AD" & maxRow + 14) = wBookSvy.Range("Q3:Q" & maxRow).Value
    Surveys.Range("AE17:AE" & maxRow + 14) = wBookSvy.Range("R3:R" & maxRow).Value
    Surveys.Range("AF17:AF" & maxRow + 14) = wBookSvy.Range("S3:S" & maxRow).Value
    Surveys.Range("AG17:AG" & maxRow + 14) = wBookSvy.Range("T3:T" & maxRow).Value
    Surveys.Range("AH17:AH" & maxRow + 14) = wBookSvy.Range("U3:U" & maxRow).Value
    
    
    ' GRAB RUN NUMBER
    Dim RunNo
    Range("D367").End(xlUp).Select
    b = ActiveCell.row
    RunNo = Sheet27.Range("B11").Value
    cells(b, 2).Value = RunNo
    
    wBook.Close
    
    'Switch back to the template sheet. Copy bit projection if given, else copy straight line
    Sheets("Survey Email").Activate
        ActiveSheet.Unprotect "LDD3006!"

    Range("B4") = maxRow + 14   'Survey sheet last row
    
    'take row of PTB then subtract your maxrow # not the + part
        Surveys.Range("D369") = Surveys.Range("D" & maxRow + 14) + Range("B12").Value 'B12 is the survey sensor offset

    If Range("C4") = "" Then
                Surveys.Range("E369") = Range("I15").Value
    Else
                Surveys.Range("E369") = Range("C4").Value
    End If
    If Range("D4") = "" Then
                Surveys.Range("F369") = Range("I16").Value
    Else
            Surveys.Range("F369") = Range("D4").Value
    End If
    
    'Free objects from system memory
    Set wBook = Nothing
    Set wBookSby = Nothing
    Set Surveys = Nothing
    
    
    Application.Calculation = xlCalculationAutomatic
    Range("A1").Activate
    
'ENABLE BEFORE FINALIZING
'EmailDBExport

 With Application
  .EnableEvents = True
  .ScreenUpdating = True
  .DisplayAlerts = True
  End With
  
  If Sheet27.CheckBox1.Value = True Then
            Call Colgate_Survey_Export
        Else
  
    Sheets("Survey Email").Activate
    ActiveSheet.Protect "LDD3006!", UserInterfaceOnly:=True, AllowFormattingCells:=True
End If
    
End Sub
Hope you can understand it.

This is the change event on the sheet 
Private Sub Worksheet_Activate()

'ActiveSheet.Unprotect
ActiveSheet.Unprotect "LDD3006!"

Application.ScreenUpdating = False

Range("D16:D369").EntireRow.Hidden = False

'For i = 18 To 267
'    If Sheet7.Range("D" & i) = "" And Sheet7.Range("D" & i + 1) = "" And Sheet7.Range("D" & i - 1) = "" Then
'        Sheet7.Rows(i).EntireRow.Hidden = True
'    Else
'        Sheet7.Rows(i).EntireRow.Hidden = False
'    End If
'Next i

'If (Range("D267") = "") Then Sheet7.Rows(267).EntireRow.Hidden = True

'This is faster in that it finds last survey and hides all rows below at once
Range("D368").End(xlUp).Select
i = ActiveCell.row
If i > 366 Then GoTo 98
Range("D" & i + 2, "D368").EntireRow.Hidden = True

98:
Sheet7.Rows(368).EntireRow.Hidden = True
Sheet7.Rows(369).EntireRow.Hidden = False
                        'Sheet7.Columns("U:V").Hidden = True
Application.ScreenUpdating = True

GoTo 99


99:

'ActiveSheet.Protect
ActiveSheet.Protect "LDD3006!"

End Sub