hi,

i am trying to loop through all files in a folder (each file being labelled MyFile), copy a chunk of stuff from each MyFile, paste that onto another file ("main.xlsx"), then copy a cell from MyFile to rename the particular worksheet in main.xlsx. i have found a useful chunk of code to loop through all files in a folder at http://mariaevert.dk/vba/?p=99, and have modified it slightly, to get:



Sub ListFiles()

Dim fd As FileDialog
Dim PathOfSelectedFolder As String
Dim SelectedFolder
Dim SelectedFolderTemp
Dim MyPath As FileDialog
Dim fs
Dim ExtraSlash
ExtraSlash = "\"
Dim MyFile
Dim i As Long
i = 1

'Prepare to open a modal window, where a folder is selected
Set MyPath = Application.FileDialog(msoFileDialogFolderPicker)
With MyPath
'Open modal window
        .AllowMultiSelect = False
        If .Show Then
            'The user has selected a folder

            'Loop through the chosen folder
            For Each SelectedFolder In .SelectedItems

                'Name of the selected folder
                PathOfSelectedFolder = SelectedFolder & ExtraSlash

                Set fs = CreateObject("Scripting.FileSystemObject")
                Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder)

                    'Loop through the files in the selected folder
                    For Each MyFile In SelectedFolderTemp.Files
                        'Name of file
                        'MsgBox MyFile.Name
                        'DO STUFF TO THE FILE, for example:
                        'Open each file:
                        Workbooks.Open Filename:=MyFile
                        Range("C19:F200").Select
                        Selection.Copy
                        Workbooks("main.xlsx").Worksheets("Sheet" & i).Activate
                        Range("a2").Select
                        ActiveSheet.Paste
                        Windows(MyFile).Activate
                        Range("C4").Select
                        Application.CutCopyMode = False
                        Selection.Copy
                        ActiveWindow.Close
                        Workbooks("main.xlsx").Worksheets("Sheet" & i).Activate
                        Sheets("Sheet" & i).Name = C4
                        i = i + 1
                    Next

            Next
        End If
End With

End Sub
there are 2 errors in this code that i do not know how to correct:

1. Windows(MyFile).Activate
can someone pls show me how to activate MyFile again within the loop?

2. Sheets("Sheet" & i).Name = C4
i would like to rename the activesheet in main.xlsx to the text that is in C4 in MyFile; how do i do that?

thanks for any help!