Copy Header to all the headers of the workbooks in a specific folder.

[countryfan_nt]

Greetings, Hope all is well.
Please help me. I have a sheet called "Headers" I want to please copy all the range of A1:R1, and paste it on a all the headers of all the workbooks in the folder:
C:\Users\nathan.pure\Desktop\Labs 365

1. The code isn't working, here is the error: "Runtime error '1004': PasteSpecial method of Range class failed".
2. one more thing, please instead of physically choosing the folder, can you please help tweak the code to go straight to the path? C:\Users\nathan.pure\Desktop\Labs 365

Truly appreciate it in advance.

Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
            
    Sheets("HEADERS").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
   
    Selection.Copy
    
    ' opens the desired folder    
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.csv*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
                        
' Code not pasting    
Range("A1").PasteSpecial Paste:=xlPasteValues
            
      ActiveWorkbook.Save
      ActiveWorkbook.Close
      
            
            End With
            xFileName = Dir
        Loop
    End If
End Sub

 

[DanteAmor]

Hi @countryfan_nt . Thanks for posting on MrExcel.

The error is because after saving a file the memory is deleted, so in the next file you want to paste but there is nothing in the memory anymore; then you have to copy the headers for each file.

Try the following code with what you requested.

Sub LoopThroughFiles()
  Dim xFileName As String, sPath As String
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  sPath = "C:\Users\nathan.pure\Desktop\Labs 365\"   'fit the desired folder
  If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
 
  xFileName = Dir(sPath & "*.csv*")
  Do While xFileName <> ""
    With Workbooks.Open(sPath & xFileName)
      ThisWorkbook.Sheets("HEADERS").Range("A1:R1").Copy
      Range("A1").PasteSpecial Paste:=xlPasteValues
      ActiveWorkbook.Save
      ActiveWorkbook.Close
    End With
    xFileName = Dir()
  Loop
 
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[countryfan_nt]

RockStar! Thank you