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

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
758
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.

VBA Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
VBA Code:
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
--------------​
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top