Copy and Paste Special within all Worksheets

[E5254730]

Hello Experts,

I've a folder Name "Output Files", into which I keep multiple worksheets.

Into Each worksheets there could be 3-5 tabs. So into this tabs there are some formulas.

I've a Macro file which extracts the data from 2nd tab of every worksheet and starts pasting the output into the Macro file.

But I continuously get a message box as "We can't update some of the links in your workbook right now" and "This workbook contains links to one or more external sources that could be unsafe".

Either I want this message box to get disabled OR

before running the Macro, I want some macros, which will go into "Output Files" folder and remove all formulas from all worksheets available into that folder.

Is there a way on how to achieve this ?

Thanks

This is the Code that is used to collect the data from multiple spreadsheets, which is kept in "Output Folder"

Sub ImportWorkBooksData()

   Dim sFile As String
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim rowTarget As Long
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, j As Long
    Dim rng As Range

   FOLDER_PATH = ThisWorkbook.Path & "\Output Files\"
   
   With CreateObject("Scripting.FileSystemObject")
    If Not .FolderExists(FOLDER_PATH) Then
    MsgBox "Specified folder does not exist"
    Exit Sub
    End If
   End With

   ThisWorkbook.Sheets("Master_Data").Unprotect "111**"
   
   If Sheets("Master_Data").AutoFilterMode Then
      Sheets("Master_Data").Range("A2").AutoFilter
   End If

   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   Set wsTarget = ThisWorkbook.Sheets("Master_Data")
   
   sFile = Dir(FOLDER_PATH & "*.xls*")
   Do Until sFile = ""
      
      Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
      Set wsSource = wbSource.Worksheets("Machining")
      
      Set rng = wsSource.Range("C1:C" & wsSource.Range("C1045000").End(xlUp).Row)
      rng.Value = Application.Trim(rng)
      
      Set rng1 = wsSource.Range("D1:D" & wsSource.Range("C1045000").End(xlUp).Row)
      rng1.Value = Application.Trim(rng1)
      
      With wsTarget
         wsSource.UsedRange.Value = wsSource.UsedRange.Value
         
         For i = 8 To wsSource.Range("C1045000").End(xlUp).Row
             If IsNumeric(wsSource.Cells(i, 3)) = True Then
                wsSource.Cells(i, 3).ClearContents
             End If
         Next i
         
         For i = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(i, 3) = "" Then
                wsSource.Cells(i, 3).Value = wsSource.Cells(i - 1, 3).Value
             End If
         Next i
         
         lrow = .Range("A1045000").End(xlUp).Row + 1
         
         .Range("A" & lrow).Value = wsSource.Range("F7").Value
         .Range("B" & lrow).Value = wsSource.Range("F5").Value
         .Range("C" & lrow).Value = wsSource.Range("F6").Value
         .Range("D" & lrow).Value = wsSource.Range("D4").Value
         .Range("E" & lrow).Value = wsSource.Range("F8").Value
         .Range("F" & lrow).Value = wsSource.Range("F9").Value
     

         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 3).Value = "Machining-2" Or wsSource.Cells(k, 3).Value = "Machining 2" Or wsSource.Cells(k, 3).Value = "Machining2" Then
                wsSource.Cells(k, 3).Value = "Machining2"
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 3).Value = "Machining-1" Or wsSource.Cells(k, 3).Value = "Machining 1" Or wsSource.Cells(k, 3).Value = "Machining1" Then
                wsSource.Cells(k, 3).Value = "Machining1"
             End If
         Next k
         
    lastRow1 = wsSource.Cells(wsSource.Rows.Count, "C").End(xlUp).Row
    lastRow2 = ThisWorkbook.Sheets("Master_Details").Cells(ThisWorkbook.Sheets("Master_Details").Rows.Count, "A").End(xlUp).Row

    For i = 1 To lastRow2
        For j = 1 To lastRow1
            If ThisWorkbook.Sheets("Master_Details").Cells(i, 1) = wsSource.Cells(j, 3) And ThisWorkbook.Sheets("Master_Details").Cells(i, 2) = wsSource.Cells(j, 4) Then
                ThisWorkbook.Sheets("Master_Details").Cells(i, 3).Value = wsSource.Cells(j, 6).Value
                Exit For
            End If
        Next j
    Next i
    
    ThisWorkbook.Sheets("Master_Details").Range("C2:C410").Copy
    ThisWorkbook.Sheets("Master_Data").Range("G" & ThisWorkbook.Sheets("Master_Data").Range("A1045000").End(xlUp).Row).PasteSpecial xlPasteValues, Transpose:=True
    CutCopyMode = False
    ThisWorkbook.Sheets("Master_Details").Range("C2:C410").ClearContents
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 3).Value = .Range("PI2").Value Then   'Total Cost
                .Range("PI" & lrow).Value = wsSource.Cells(k, 4).Value
             End If
         Next k
         
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("OZ2").Value Then   'Process
                .Range("OZ" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PA2").Value Then
                .Range("PA" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PB2").Value Then
                .Range("PB" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PC2").Value Then
                .Range("PC" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PD2").Value Then
                .Range("PD" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PE2").Value Then
                .Range("PE" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PF2").Value Then
                .Range("PF" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PG2").Value Then
                .Range("PG" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
         
         For k = 12 To wsSource.Range("C1045000").End(xlUp).Row
             If wsSource.Cells(k, 4).Value = .Range("PH2").Value Then   'Unit Cost
                .Range("PH" & lrow).Value = wsSource.Cells(k, 6).Value
             End If
         Next k
        
         .Range("PJ" & lrow).Value = sFile      'Source File
      End With
  
      wbSource.Close SaveChanges:=False
      sFile = Dir()
   Loop
   
   RemBorder
   BorderYes
ThisWorkbook.Sheets("Master_Data").Range("A2").AutoFilter
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
   
   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
   
ThisWorkbook.Sheets("Master_Data").Protect "111**", UserInterfaceOnly:=True, AllowFiltering:=True
End Sub

[Mumps1]

Try:

Set wbSource = Workbooks.Open(FOLDER_PATH & sFile, UpdateLinks:=False)