For some reason this code works at home two test files, but when running at work I get this error. The macro loops through a list of sheet names in the macrobook and in the wb2 it should select all cells and copy paste as values, which doesn't happen because of this error. Then it deletes all sheets if the sheet name is not in the list in the macrobook. It does delete the sheets properly. Maybe the solution would be to add another code that loops through the remaining sheets and copy paste as values? Or does someone know what I'm doing wrong here. 


Sub NewWeek_4()
    Dim Macrobook As Workbook
    Dim wb2 As Workbook
    Dim rngCell As Range
    Dim macrosheet As Worksheet
    Dim ws2 As Worksheet
    
    Application.ScreenUpdating = False
    Set Macrobook = ActiveWorkbook
    Set macrosheet = Macrobook.ActiveSheet
    
     Set wb2 = Workbooks.Open(Filename:="O:\2\folder01\Analysis 0430.xlsx")
    For Each ws2 In wb2.Worksheets
      If WorksheetFunction.CountIf(macrosheet.Range("A:A"), ws2.Name) > 0 Then
         With ws2
          For Each rngCell In .UsedRange
              *If Trim(rngCell.Value) <> vbNullString Then* rngCell.Value = Trim(rngCell.Value)
          Next rngCell
        End With
      Else
        Application.DisplayAlerts = False
        ws2.Delete
        Application.DisplayAlerts = True
      End If
    Next ws2
   
    wb2.Save
    macroobk.Activate
    Application.Goto macrosheet.Cells(1)
    Application.ScreenUpdating = True
    Set rngCell = Nothing
    Set wb2 = Nothing
    Set wsAct = Nothing
    Set myWb = Nothing
     
End Sub