Macro to choose folder and cycle through all files

[Mr.Pinches]

Thanks in advance.

I have a workbook that contains many control charts and currently it requires someone to open each file in a folder to enter all the data into the workbook using a hot-key'd macro. I'd like to be able to cut down the work involved and just have the user choose the folder so that a similar macro could copy all the data into the workbook where the macros already in place would be able to compile it.

While I've seen may similar questions on this forum. This code http://www.excelforum.com/forum-rule...rum-rules.htmlappears to be what I believe I need,so I've altered it to my needs.


I'm not sure why the code below will not work:

Sub Runfolder()

Dim screenUpdateState       As Variant
Dim statusBarState          As Variant
Dim eventsState             As Variant
Dim fso                     As Object
Dim fPath                   As String
Dim myFolder, myFile
Dim wb                      As Workbook
Dim SavePath                As String
Dim I, x                    As Integer
Dim ws                      As Worksheet

' Turn off some Excel functionality so your code runs faster
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    eventsState = Application.EnableEvents

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

' Use File System Object to choose folder with files
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
    End With

' Open each file consequently
        Set myFolder = fso.GetFolder(fPath).Files
            For Each myFile In myFolder
                If LCase(myFile) Like "*.xls*" Then
                    For I = 1 To x

' Perform tasks with each file
                        Set wb = Workbooks.Open(myFile)
                        With wb.Worksheets("summary")
      'my changes
      Sheets("Summary").Select
     Cells.Select
    Selection.Copy
    Windows("Stats2011.xlsm").Activate
    Sheets("Paste").Select
    Cells.Select
    ActiveSheet.Paste
                           
                            
                            
                        End With
                        
                 
                       
                        ' Close file
                        wb.Close True

' Loop through all files in folder
                    Next I
                End If
            Next myFile

'clean up
    myFile = vbNullString
    I = 1

' Turn Excel functionality back on
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.EnableEvents = eventsState

End Sub