VBA: Copy sheets from separte files into another workbook?

[cineno]

Hello.

I'm in urgent need of help here. Sorry if this question is elementary, I'm new to this.

In VBA I need to be able to have code that opens each Excel file in a folder, Selects All, Copies it, and pastes it into a new sheet on my main workbook. So basically one workbook would have all the data from separte workbooks, on individual sheets.

These separate workbooks only have one sheet of data I want to copy named "Main." So I need one workbook to have all the "Main" sheets from separte workbooks, pasted onto individual sheets.

Does anyone know of any code that can do this? Any help would be greatly appreciated! Thank you.

 

[tstevens]

It might be even easier than wiring code to just record a macro that does this. Record what your doing in a Workbook that you can save as a template. Then as it's recording go through the steps of what your want done.

Hope this helps,
Tyler

 

[Joe Was]

Sub allWorkbook()
'Standard Module code, like: Module1!
Dim f%, Wb

Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch

'Option: Search Sub-Folders as well?
.SearchSubFolders = False 'Option: True or False!

'Option Current Folder or a defined folder?
.LookIn = CurDir
'Or
'.LookIn = "C:\myFolderNameHere"

'Option: Only Search this type of file?
.Filename = "*.xls"

.Execute

For f = 1 To .FoundFiles.Count
Set Wb = Workbooks.Open(Filename:=.FoundFiles(f))

'*************************************************************************
'Add your code her!
'*************************************************************************

ActiveWorkbook.Save
ActiveWorkbook.Close

Next f
End With

Application.ScreenUpdating = True
End Sub

 

[tstevens]

Sorry, but do the workbooks (from which your gathering the information) names change?

 

[cineno]

thanks for the help so far. I'll try some of the suggestions.

No the names won't change. This is just something I got to do once. There's just so many workbooks that if I was to do it by hand it would take way too long.

Anyone have any other suggestions?

 

[cineno]

I got it now. Thanks for all the help!