Macro to copy data from one workbook to multiple workbooks in a specific folder

Question

This is exactly what I wanted but after changing the folder path file name etc. for my needs, it doesn't work.

My workbook's path is C:\Users\navinc\Desktop\
My file name is test(2)
My worksheet name is Master Project List
My target files in folder path C:\Users\navinc\Desktop\test\
Error message : "Subscript out of range" Is there anything i'm missing ?

My code :

Sub Macro1()
   Dim file As String
   Dim myPath As String
   Dim wb As Workbook
   Dim rng As Range

   Dim wbMaster As Workbook
   'if master workbook already opened
   'Set wbMaster = Workbooks("test(2).xlsx")
   'if master workbook is not opened
   Set wbMaster = Workbooks.Open("C:\Users\navinc\Desktop\test(2).xlsx")

   Set rng = wbMaster.Sheets("Master Project list").Range("A1:D1")

   myPath = "C:\Users\navinc\Desktop\test\" ' note there is a back slash in the end
   file = Dir(myPath & "*.xlsx*")
   While (file <> "")

        Set wb = Workbooks.Open(myPath & file)
        rng.Copy
        With wb.Worksheets("Master Project list").Range("A1")
            .PasteSpecial xlPasteColumnWidths
            .PasteSpecial xlPasteAll
        End With

        wb.Close SaveChanges:=True
        Set wb = Nothing

        file = Dir
    Wend

    Application.CutCopyMode = False
End Sub

error with this line With wb.Worksheets("MasterProjectlist").Range("A1") the thing is that the taget files will have different sheet names. — Navin
Seems like wb doesn't have a sheet named "Master Project list" ? If you don't know what the target sheet name will be then that's going to be tricky unless (eg) it's always the first worksheet you want, in which case you can use With wb.Worksheets(1).Range("A1") — Tim Williams
OMG !!!! It works !! Thank you soooooooo much... you made my day !!! — Navin

Tim Williams Tim Williams · Accepted Answer · 2016-03-01T18:27:44

Seems like wb doesn't have a sheet named "Master Project list" ?

If you don't know what the target sheet name will be then that's going to be tricky unless (eg) it's always the first worksheet you want, in which case you can use:

With wb.Worksheets(1).Range("A1")

Macro to copy data from one workbook to multiple workbooks in a specific folder

1 Answers