Copy Data from one file, then loop through a folder and paste the data in each file


New Member
Dec 21, 2016
Hey friends,

I am having trouble with my excel vba code to perform this macro. I get an error on the line that I put in red text. Please help!

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

   Dim wbMaster As Workbook
   Set wbMaster = Workbooks("workbook1.xlsm")
   Set rng = wbMaster.Sheets("Support Data").Range("A:Z")

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

        Set wb = Workbooks.Open(myPath & file)
[COLOR=#ff0000]        With wb.Worksheets("Support Data").Range("A:Z")[/COLOR]
            .PasteSpecial xlPasteColumnWidths
            .PasteSpecial xlPasteAll
        End With

        wb.Close SaveChanges:=True
        Set wb = Nothing

        file = Dir

    Application.CutCopyMode = False
End Sub

Public Sub CopySheetToAllWorkbooksInFolder()

    Dim sourceSheet As Worksheet
    Dim folder As String, filename As String
    Dim destinationWorkbook As Workbook
    On Error Resume Next
    'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
    'Worksheet in active workbook to be copied as a new sheet to all workbooks
    Set sourceSheet = ActiveWorkbook.Worksheets("Support Data")
    'Folder containing the workbooks
    folder = "C:\Users\jlovat\Documents\"
    filename = Dir(folder & "*.xls", vbNormal)
    While Len(filename) <> 0
        Debug.Print folder & filename
        Set destinationWorkbook = Workbooks.Open(folder & filename)
        sourceSheet.Copy after:=destinationWorkbook.Sheets("Sheet1")
        destinationWorkbook.Close True
        filename = Dir()  ' Get next matching file

End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...