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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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?
 

Forum statistics

Threads
1,141,913
Messages
5,709,292
Members
421,625
Latest member
Natalie1107

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top