Copy worksheets of files o different directories into one workbook

R

rence19

New Member
Joined
Mar 5, 2011
Messages
43
hello friends,

kindly help me with this.i need a macro for my project.

i want to have a single file which contains all worksheets from multiple workbooks of the same filename but different worksheets. this files have different directories.

say:

file1.xls (worksheet names here is "aaa,abc") in d:\\my_file
file1.xls(worksheet names here is "bbb,bdg") in c:\\my_saved_files
.
.
.file1.xls(worksheet names here is "zzz,yyy" ) in d:\\my_other_file
.
.

appreciate all the help!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi, and welcome to the board.

The way I have approached your problem is to put all of your file paths into an array - you will need to edit these:

Code: 
   [COLOR=green]'array of file paths[/COLOR]
   aPath = Array("c:\temp\file1.xls", _
               "c:\temp\sub-folder1\file1.xls", _
               "c:\temp\sub-folder2\file1.xls")

I then loop through the array, open each workbook and copy the worksheets.

Code: 
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aPath) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aPath)
      [COLOR=green]'open each workbook in the array[/COLOR]
      [COLOR=green]'and copy the worksheets[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(aPath(i))
      [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
         counter = counter + 1
         ws.Copy After:=Sheets(Sheets.Count)
         ActiveSheet.Name = ws.Name & "_" & counter
      [COLOR=darkblue]Next[/COLOR] ws
 
      [COLOR=green]'close the workbook[/COLOR]
      wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i

To ensure each imported sheet has a unique name I have used a "counter" variable to add a number to each sheet's name.

The full code is shown below.

To use, Open Excel
Press Alt+F11
Double click the ThisWorkbook module in the Project window on the left hand side.
Copy and paste the code.
Press F5 to run.

NB Remember to edit the file paths.

Code: 
[COLOR=darkblue]Sub[/COLOR] ImportWorksheets2()
   [COLOR=darkblue]Dim[/COLOR] aPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]    [COLOR=green]'array of file paths[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]        [COLOR=green]'loop index[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]     'file to open
   [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]     [COLOR=green]'append to sheet name for unique name[/COLOR]
 
   [COLOR=green]'array of file paths[/COLOR]
   aPath = Array("[COLOR=red]c:\temp\file1.xls[/COLOR]", _
               "[COLOR=red]c:\temp\sub-folder1\file1.xls[/COLOR]", _
               "[COLOR=red]c:\temp\sub-folder2\file1.xls")[/COLOR]
 
   [COLOR=green]'cater for the file doesn't exist[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aPath) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aPath)
      [COLOR=green]'open each workbook in the array[/COLOR]
      [COLOR=green]'and copy the worksheets[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(aPath(i))
      [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
         counter = counter + 1
         ws.Copy After:=Sheets(Sheets.Count)
         ActiveSheet.Name = ws.Name & "_" & counter
      [COLOR=darkblue]Next[/COLOR] ws
 
      [COLOR=green]'close the workbook[/COLOR]
      wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
thanks mate for your kindness.

by the way this is the more detailed description what i need:

I.
all of the file in each directory have same filename ("file1.xls")

II.
c:\temp\"file1.xls" (this file has variable number of sheets with unique sheetnames ie: name;address;age;......)

c:\temp\sub-folder2\"file1.xls" (this file has variable number of sheets with unique sheetnames ie: time_records;.....)

c:\temp\sub-folder3\"file1.xls" (this file has variable number of sheets with unique sheetnames ie: overtime_rendered;.... )

III.
consolidate file to be saved into new directory say:
c:\my_database\"file1.xls" ; having all the sheets from file1.xls of different directories as above.

c:\my_database\"file1.xls" ; w/ sheetsnames: name;address;age;time_records;overtime_rendered....etc

IV.
delete the source files
c:\temp\"file1.xls"
c:\temp\sub-folder2\"file1.xls"
c:\temp\sub-folder3\"file1.xls"
:)
 
Upvote 0
Hi is there a way to copy the content of multiple worksheets from different workbooks into one worksheet without actually opening those multiple workbooks. ? I am asking this coz if you have say 20-25 workbooks opening them will take lot of time.
 
Upvote 0
navb said:
Hi is there a way to copy the content of multiple worksheets from different workbooks into one worksheet without actually opening those multiple workbooks. ? I am asking this coz if you have say 20-25 workbooks opening them will take lot of time.
Click to expand...

Yes there is. It involves using ADO and accessing the files via the, "Jet Engine".

Can I ask you to please start a new topic for this.

Thanks, Bertie
 
Upvote 0
Hi Rence,

Points 1 and 2 we have addressed with the code I submitted.

Point 4 is simple enough, but do you really want to delete data source files?

Point 3
consolidate file to be saved into new directory say:
c:\my_database\"file1.xls" ; having all the sheets from file1.xls of different directories as above.
c:\my_database\"file1.xls" ; w/ sheetsnames: name;address;age;time_records;overtime_rendered....etc
Click to expand...

I am not quite sure what you mean here.

In the code I submitted the worksheets from the three files were saved as individual worksheets in the consolidated file. Is this what you want?
 
Upvote 0
hi mate,

sorry for the confusion.

By the way this is the saving directory of the results (same filename "file1.xls") having all the sheets consolidated from the different directories of same filename (file1.xls).
i need to remove those "raw data" to come up with this single file.

c:\my_database\"file1.xls" ; w/ sheetsnames: name;address;age;time_records;overtime_rendered....etc

thank you so much!!!
 
Upvote 0
To delete the data source workbook you would use the KILL command. I have highlighted where this command goes in the code and commented it out. I would advise to make a back-up of your source data before testing this.

I have also highlighted the save routine, Test this first before uncommenting the KILL command.

Code: 
[COLOR=darkblue]Sub[/COLOR] ImportWorksheets2()
   [COLOR=darkblue]Dim[/COLOR] aPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]    [COLOR=green]'array of file paths[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]        [COLOR=green]'loop index[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]     'file to open
   [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]     [COLOR=green]'append to sheet name for unique name[/COLOR]
 
   [COLOR=green]'array of file paths[/COLOR]
   aPath = Array("c:\temp\file1.xls", _
               "c:\temp\sub-folder1\file1.xls", _
               "c:\temp\sub-folder2\file1.xls")
 
   [COLOR=green]'cater for the file doesn't exist[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aPath) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aPath)
      [COLOR=green]'open each workbook in the array[/COLOR]
      [COLOR=green]'and copy the worksheets[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(aPath(i))
      [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
         counter = counter + 1
         ws.Copy After:=Sheets(Sheets.Count)
         ActiveSheet.Name = ws.Name & "_" & counter
      [COLOR=darkblue]Next[/COLOR] ws
 
      [COLOR=green]'close the workbook[/COLOR]
      wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
 
      [COLOR=green]'delete the data source[/COLOR]
[COLOR=red]     'Kill aPath(i)[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
[COLOR=red]  'copy and save the consolidated workbook[/COLOR]
[COLOR=red]  With ActiveWorkbook[/COLOR]
[COLOR=red]     .Copy[/COLOR]
[COLOR=red]     .SaveAs Filename:="c:\my_database\file1.xls"[/COLOR]
[COLOR=red]     .Close SaveChanges:=False[/COLOR]
[COLOR=red]  End With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
hi mate,

this indeed worked!!!!
i followed the instructions "ALT F11- THIS WORKBOOK -(paste source code).

In my case want to save it together with my other macro on just one workbook and save it in module. In this workbook, i have drop-down list and command button.

i want to make one command button for this particular project that when i need to have that consolidated file, i just have to click the button and have it. And by the way, can the filenames be made variable in such a way that what ever value selected on the dropdown list then that will be the name of "file1.xls" in:

aPath = Array("c:\temp\file1.xls", _
"c:\temp\sub-folder1\file1.xls", _
"c:\temp\sub-folder2\file1.xls")

.....
i tried to modify the code by adding:

fname = ActiveWorkbook.Worksheets("Sheet1").Cells(5, 4)
'this active workbook is my own Workbook

and

aPath = Array("c:\temp\" & fname & ".xls", _
"c:\temp\sub-folder1\" & fname & ".xls", _
"c:\temp\sub-folder2\" & fname & ".xls")

and i ended up having my workbook saved in the set saving directory.
im getting closer to this. appreciate every help!

thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
153
druck21
D
G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
276
gjvv
G
J
  • Question Question
Append data to master file
Replies
6
Views
352
Alex Blakenburg
A
B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
707
Joe4
Joe4
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
150
howard
H

Forum statistics

Threads
1,224,518
Messages
6,179,258
Members
452,901
Latest member
LisaGo

Share this page

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
Back
Top