VBA: Copy sheets from separte files into another workbook?

cineno

New Member
Joined
Jun 27, 2006
Messages
35
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

tstevens

New Member
Joined
Sep 1, 2006
Messages
29
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Sep 1, 2006
Messages
29
Sorry, but do the workbooks (from which your gathering the information) names change?
 

cineno

New Member
Joined
Jun 27, 2006
Messages
35
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,057
Messages
5,545,753
Members
410,704
Latest member
Cobber2008
Top