VBA to copy a row of Excel files from one folder to another

[Bamh1]

Hello,

I have two folders each containing equal number files (n=20) with similar file names. I want to copy the first row of the files in Folder1 to the files of Folder 2. I have written the below VBA code, but it's not working:

Sub CopyData()
Dim wk1, wk2 As Workbook
Dim MyDir1, MyDir2 As Variant
Dim MyFile1, MyFile2 As String
Dim i As Integer


Application.ScreenUpdating = True
Application.DisplayAlerts = False

MyDir1 = "D:\Path to Folder1\"
MyDir2 = "D:\Path to Folder2\"
MyFile1 = Dir(MyDir1 & "*.xlsx")
MyFile2 = Dir(MyDir2 & "*.xlsx")
'Open workbooks
Set wk1 = Workbooks.Open(MyDir1)
Set wk2 = Workbooks.Open(MyDir2)
For i = 1 To 20
If MyFile1.FileName = MyFile2.FileName Then:
wk1.Sheets(1).Rows("1").Copy Destination:=wk2.Sheets(1).Rows("1")
wk1.Close
wk2.Save
wk2.Close True
i = i + 1
Next
'Turn on screen updating
Application.ScreenUpdating = True

End Sub

Could someone please help fixing this code.

Thank you,

Shawn

 

[John_w]

I have two folders each containing equal number files (n=20) with similar file names. I want to copy the first row of the files in Folder1 to the files of Folder 2.

Similar file names or the same file names between the folders? If the latter then Excel doesn't allow 2 workbooks with the same name to be open at the same time, so this macro temporarily renames the file in Folder2.

Public Sub Copy_Row_Between_Workbooks_In_2_Folders()

    Dim folder1 As String, folder2 As String
    Dim fileName As String
    Dim wb1 As Workbook, wb2 As Workbook
   
    folder1 = "D:\Path to Folder1\"
    folder2 = "D:\Path to Folder2\"
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    fileName = Dir(folder1 & "*.xlsx")
    While fileName <> vbNullString
        Name folder2 & fileName As folder2 & "COPY " & fileName
        Set wb1 = Workbooks.Open(folder1 & fileName)
        Set wb2 = Workbooks.Open(folder2 & "COPY " & fileName)
        wb1.Worksheets(1).Rows("1").Copy Destination:=wb2.Worksheets(1).Rows("1")
        wb1.Close False
        wb2.Close True
        Name folder2 & "COPY " & fileName As folder2 & fileName
        fileName = Dir
    Wend
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
End Sub

 

[Bamh1]

Similar file names or the same file names between the folders? If the latter then Excel doesn't allow 2 workbooks with the same name to be open at the same time, so this macro temporarily renames the file in Folder2.

Public Sub Copy_Row_Between_Workbooks_In_2_Folders()

    Dim folder1 As String, folder2 As String
    Dim fileName As String
    Dim wb1 As Workbook, wb2 As Workbook
  
    folder1 = "D:\Path to Folder1\"
    folder2 = "D:\Path to Folder2\"
  
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
  
    fileName = Dir(folder1 & "*.xlsx")
    While fileName <> vbNullString
        Name folder2 & fileName As folder2 & "COPY " & fileName
        Set wb1 = Workbooks.Open(folder1 & fileName)
        Set wb2 = Workbooks.Open(folder2 & "COPY " & fileName)
        wb1.Worksheets(1).Rows("1").Copy Destination:=wb2.Worksheets(1).Rows("1")
        wb1.Close False
        wb2.Close True
        Name folder2 & "COPY " & fileName As folder2 & fileName
        fileName = Dir
    Wend
  
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
  
End Sub

Hi John,

Thank you for taking the time to review this request, and for the tip about Excel not opening the same names. I ran your code, but I get an error on this line: Name folder2 & fileName As folder2 & "COPY " & fileName

 

[John_w]

You didn't answer my question.

What is the error? Do both folder paths end with a back slash character? If you have "File1.xlsx" in folder1, do you also have "File1.xlsx" in folder2?

 

[Bamh1]

You didn't answer my question.

What is the error? Do both folder paths end with a back slash character? If you have "File1.xlsx" in folder1, do you also have "File1.xlsx" in folder2?

Yes, file names ARE exactly the same. The first row of File1.xlsx in folder 1 should be copied to File1.xlsx in folder 2 and so on. The error is: Run time error'53' File not found, and debugger highlights this line: Name folder2 & fileName As folder2 & "COPY " & fileName

 

[Bamh1]

They don't have the backslash at the enn, but when I removed the backslashes, it still won't work

 

[John_w]

They don't have the backslash at the enn,

They should have back slashes, as coded.

The error suggests File1.xlsx exists in folder1, but not in folder2. Add this line above the Name ... line and verify that the file displayed actually exists.

        MsgBox "Rename " & folder2 & fileName

 

[Bamh1]

That was true, I found and removed the additional file and the code started executing. Thank you for your help