The bad news is, based on your comments above, you have a moderate amount of work ahead of you before you will be able to write this program. The good news is the program itself will be relatively simple, and the knowledge you obtain in the process will prove extremely useful (and if you enjoy learning it should be fun, too).
A basic overview of what you'll want your program to do to accomplish your task is:
- Tell your program where the source files are stored (presumably they are all stored in a directory together; this will make it a lot easier)
- Open each document one at a time
- Copy and paste the sheet you are interested in (this can be done in one statement)
- Close the document
- Repeat 2-4
- When finished, exit the procedure
The best way to do this is to split up the whole thing into pieces. The first/top piece will look something like this:
Sub CopyAllTheUserWorksheets()
'Get the folder/directory location with your source files:
Dim MyDirectory As String
MyDirectory = SelectFolder
'SelectFolder is another function you write separately that will open a
'file dialog box and return the path of the folder you choose. If you
'cancel the file dialog, it will just return a "\"
'Now test to make sure a directory was chosen:
If MyDirectory = "\" Then Exit Sub
'Create the file name you will loop over to open all the Excel files:
Dim MyFile As String
MyFile = Dir(MyDirectory & "*.xlsx")
'Change .xlsx to .xls if your source files are the old Excel file format
'Open each file one at a time, perform the actions you want to perform,
'then close the file. You accomplish this using what is called a Loop.
'There are a handful of different kinds of Loops. For this task I suggest a
'Do While loop. Before the loop, we make a Workbook variable in which to
'store the opened workbook so we can work with it inside of the loop.
Dim wb As Workbook
Do While MyFile <> ""
'Open the Excel workbook:
Set wb = Workbooks.Open(Filename:=MyDirectory & MyFile)
'Here you put the task you want to do with the workbook.
'We'll put that in a separate procedure as well to keep things tidy.
Call CopyTheUserWorksheetToThisWorkbook(wb)
'Now that we are done copying the worksheet, close the Excel workbook
'without saving any changes
wb.Close SaveChanges:=False
'Proceed to the next file name. If there are no more files that match
'*.xlsx, then myFile will be equal to an empty string, which is "", and
'the Do While loop with end at that point.
myFile = Dir
Loop
End Sub
See if you can make sense of this. I'll add some more later (for the SelectFolder function and the CopyTheUserWorksheetToThisWorkbook procedure).
Note that this link was very helpful in formulating this answer, and might help you understand what is going on a bit better.
EDIT: Here is the SelectFolder function called by the procedure above:
Function SelectFolder() As String
'Make a string variable to hold the folder path
Dim MyFolder As String
'Make a FileDialog object for choosing the folder
Dim MyFolderChooser As FileDialog
'Make the FileDialog object
Set MyFolderChooser = Application.FileDialog(msoFileDialogFolderPicker)
'Give it a title (optional)
MyFolderChooser.Title = "Select A Target Folder"
'Disable MultiSelect (you only want one folder selected)
MyFolderChooser.AllowMultiSelect = False
'This is a normal dialog box, so it could be Cancelled, or Closed. We
'need to tell the procedure what to do if that happens. The statement
'below basically says "If the dialog is cancelled,skip everything until
'NextStep." Without this, you would get an error if you canceled or closed
'the file dialog.
If MyFolderChooser.Show <> -1 Then GoTo NextStep
'Assign the MyFolder variable the value given to the FileDialog object
MyFolder = MyFolderChooser.SelectedItems(1)
'The (1) means the first value. Since we turned MultiSelect off, there
'will be no (2), (3), etc, but VBA isn't smart enough to figure this
'out on its own so you have to tell it.
NextStep:
'Now just set the name of the function (SelectFolder) equal to the result
'you want, and that value will be returned to the procedure that called
'the function.
SelectFolder = MyFolder & "\"
'If no directory was chosen, the function will return just "\"
End Function
