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

[jlovat]

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)
        rng.Copy
[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
    Wend


    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
    Wend




End Sub
[code/]