Hi,


I have a folder with x number of workbooks, each workbook has one sheet and
each sheet has a unique name. What I want to do is to copy all the sheets
from all workbooks into a new workbook.. So if I have 10 workbooks I want to
run a macro and get a new workbook with 10 sheets (the index order of the
sheets in the new workbook is of no importance). I went to Ron De Bruins
site which is very good btw but all I could find was code to copy workbooks
in a folder and merge them in one sheet in a new workbook. I want to keep
all sheets but put them in a single workbook


Thanks,

Jan Svendesen

Jan,

try something like this:


Sub test()
Dim strPath As String
Dim strFile As String
Dim wbNew As Workbook
Dim wbOld As Workbook

strPath = "D:\Temp\"

strFile = Dir(strPath & "*.xls")

While strFile < ""
Set wbOld = Workbooks.Open(strPath & strFile)
If wbNew Is Nothing Then
wbOld.Sheets(1).Copy
Set wbNew = ActiveWorkbook
Else
wbOld.Sheets(1).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
End If
wbOld.Close False
strFile = Dir
Wend

End Sub



--
Hope that helps.

Vergel Adriano


"Jan Svendesen" wrote:

Hi,


I have a folder with x number of workbooks, each workbook has one sheet and
each sheet has a unique name. What I want to do is to copy all the sheets
from all workbooks into a new workbook.. So if I have 10 workbooks I want to
run a macro and get a new workbook with 10 sheets (the index order of the
sheets in the new workbook is of no importance). I went to Ron De Bruins
site which is very good btw but all I could find was code to copy workbooks
in a folder and merge them in one sheet in a new workbook. I want to keep
all sheets but put them in a single workbook


Thanks,

Jan Svendesen

Hi Jan,

I think that Ron also includes sample code to copy a
sheet from each workbook into a master workbook.

See, for example:

Copy sheet from each workbook
http://www.rondebruin.nl/fso.htm


---
Regards,
Norman
Microsoft Excel MVP



"Jan Svendesen" wrote in message
...
Hi,


I have a folder with x number of workbooks, each workbook has one sheet
and each sheet has a unique name. What I want to do is to copy all the
sheets from all workbooks into a new workbook.. So if I have 10 workbooks
I want to run a macro and get a new workbook with 10 sheets (the index
order of the sheets in the new workbook is of no importance). I went to
Ron De Bruins site which is very good btw but all I could find was code to
copy workbooks in a folder and merge them in one sheet in a new workbook.
I want to keep all sheets but put them in a single workbook


Thanks,

Jan Svendesen

Thanks


Jan


"Vergel Adriano" wrote in message
...
Jan,

try something like this:


Sub test()
Dim strPath As String
Dim strFile As String
Dim wbNew As Workbook
Dim wbOld As Workbook

strPath = "D:\Temp\"

strFile = Dir(strPath & "*.xls")

While strFile < ""
Set wbOld = Workbooks.Open(strPath & strFile)
If wbNew Is Nothing Then
wbOld.Sheets(1).Copy
Set wbNew = ActiveWorkbook
Else
wbOld.Sheets(1).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
End If
wbOld.Close False
strFile = Dir
Wend

End Sub



--
Hope that helps.

Vergel Adriano


"Jan Svendesen" wrote:

Hi,


I have a folder with x number of workbooks, each workbook has one sheet
and
each sheet has a unique name. What I want to do is to copy all the sheets
from all workbooks into a new workbook.. So if I have 10 workbooks I want
to
run a macro and get a new workbook with 10 sheets (the index order of the
sheets in the new workbook is of no importance). I went to Ron De Bruins
site which is very good btw but all I could find was code to copy
workbooks
in a folder and merge them in one sheet in a new workbook. I want to keep
all sheets but put them in a single workbook


Thanks,

Jan Svendesen

You are correct,

thanks

Jan

"Norman Jones" wrote in message
...
Hi Jan,

I think that Ron also includes sample code to copy a
sheet from each workbook into a master workbook.

See, for example:

Copy sheet from each workbook
http://www.rondebruin.nl/fso.htm


---
Regards,
Norman
Microsoft Excel MVP



"Jan Svendesen" wrote in message
...
Hi,


I have a folder with x number of workbooks, each workbook has one sheet
and each sheet has a unique name. What I want to do is to copy all the
sheets from all workbooks into a new workbook.. So if I have 10 workbooks
I want to run a macro and get a new workbook with 10 sheets (the index
order of the sheets in the new workbook is of no importance). I went to
Ron De Bruins site which is very good btw but all I could find was code
to copy workbooks in a folder and merge them in one sheet in a new
workbook. I want to keep all sheets but put them in a single workbook


Thanks,

Jan Svendesen

And I also add a filter example yesterday to the page

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Norman Jones" wrote in message ...
Hi Jan,

I think that Ron also includes sample code to copy a
sheet from each workbook into a master workbook.

See, for example:

Copy sheet from each workbook
http://www.rondebruin.nl/fso.htm


---
Regards,
Norman
Microsoft Excel MVP



"Jan Svendesen" wrote in message
...
Hi,


I have a folder with x number of workbooks, each workbook has one sheet
and each sheet has a unique name. What I want to do is to copy all the
sheets from all workbooks into a new workbook.. So if I have 10 workbooks
I want to run a macro and get a new workbook with 10 sheets (the index
order of the sheets in the new workbook is of no importance). I went to
Ron De Bruins site which is very good btw but all I could find was code to
copy workbooks in a folder and merge them in one sheet in a new workbook.
I want to keep all sheets but put them in a single workbook


Thanks,

Jan Svendesen