Hi,

I often receive data in several workbooks that i have to open and cop
to a new workbook so as to be able to evaluate the data in compariso
with each other.

Is it possible to use programming (or any other way) to simplify th
process so that on clicking a button or running a macro will collec
each of the sheets from the desired workbooks and copy them to a ne
workbook?

Any help would be much appreciate

--
Message posted from http://www.ExcelForum.com

Sub GetSheets()
Dim sPath as Path, i as long
Dim varr as Variant
Dim wkbk as Workbook
sPath = "C:\MyData\"
varr = ("Data1.xls", "Data2.xls", "Data3.xls")
for i = lbound(varr) to ubound(varr)
set wkbk = workbooks.open(sPath & varr(i))
wkbk.worksheets(1).Copy After:=Thisworkbook. _
Worksheets(thisworkbook.Worksheets.count)
wkbk.close SaveChanges:=False
Next
End sub

would be a simple example.

--
Regards,
Tom Ogilvy


ian123 wrote in message
...
Hi,

I often receive data in several workbooks that i have to open and copy
to a new workbook so as to be able to evaluate the data in comparison
with each other.

Is it possible to use programming (or any other way) to simplify the
process so that on clicking a button or running a macro will collect
each of the sheets from the desired workbooks and copy them to a new
workbook?

Any help would be much appreciated


---
Message posted from http://www.ExcelForum.com/

Thanks for the help. Unfortunately when running the macro the first
line is being rejected as "Compile Error- User Defined Type Not
Defined"

Any ideas as to where i'm going wrong?

Also the line "varr = (Data1.xls, Data2.xls") is highlighted in red and
i am informed that "Compile Error - Expected )" with the first ','
highlighted?


---
Message posted from http://www.ExcelForum.com/

varr = ("Data1.xls", "Data2.xls", "Data3.xls")
should be
varr = Array("Data1.xls", "Data2.xls", "Data3.xls")

--
Regards,
Tom Ogilvy

ian123 wrote in message
...
Thanks for the help. Unfortunately when running the macro the first
line is being rejected as "Compile Error- User Defined Type Not
Defined"

Any ideas as to where i'm going wrong?

Also the line "varr = (Data1.xls, Data2.xls") is highlighted in red and
i am informed that "Compile Error - Expected )" with the first ','
highlighted?


---
Message posted from http://www.ExcelForum.com/

Thanks very much, that solves the 2nd of the 2 problems but
unfortunately i am still being told that the first line of code "Dim
sPath As Path, i As Long" is a compile error, user defined type is not
defined. The words "sPath As Path" are highlighted in blue.

Any advice?


---
Message posted from http://www.ExcelForum.com/

sPath as String

--
Regards,
Tom Ogilvy

ian123 wrote in message
...
Thanks very much, that solves the 2nd of the 2 problems but
unfortunately i am still being told that the first line of code "Dim
sPath As Path, i As Long" is a compile error, user defined type is not
defined. The words "sPath As Path" are highlighted in blue.

Any advice?


---
Message posted from http://www.ExcelForum.com/

Thanks very much, i can now get it to bring the first sheet of each book
in the range selected. Is it possible to bring all the sheets from
each of the books selected into the new book?

Once again thanks very much for your help on this - it is very much
appreciated


---
Message posted from http://www.ExcelForum.com/

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "C:\MyData\"
varr = Array("Data1.xls", "Data2.xls", "Data3.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)
wkbk.Close SaveChanges:=False
Next
End Sub

--
Regards,
Tom Ogilvy


ian123 wrote in message
...
Thanks very much, i can now get it to bring the first sheet of each book
in the range selected. Is it possible to bring all the sheets from
each of the books selected into the new book?

Once again thanks very much for your help on this - it is very much
appreciated


---
Message posted from http://www.ExcelForum.com/

Thanks - that's fantastic. Your help is very much appreciate

--
Message posted from http://www.ExcelForum.com

Thats a great macro... Is there a way to tweak it so that it will
combine approx 300 xl files with different names (of a sequence) into 1
file?

Could it also be modified to put the data in the next available column
of the same sheet?


Thanks!


---
Message posted from http://www.ExcelForum.com/

Change this line:

wkbk.Worksheets(1).Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)

to

wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)



ian123 wrote:

Thanks very much, i can now get it to bring the first sheet of each book
in the range selected. Is it possible to bring all the sheets from
each of the books selected into the new book?

Once again thanks very much for your help on this - it is very much
appreciated

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

I have been monitoring this thread with considerable interest. I've
been trying to figure out what code needed after they are copied to
rename each worksheet using a numeric value that appears in cell C2 of
each sheet as the name. The value in cell C2 is unique for each
worksheet. Any help is appreciated.

The code I am using is as follows:

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "C:\Data\DataFiles\test\"
varr = Array("Data1.xls", "Data2.xls", "Data3.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets(6).Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)
wkbk.Close SaveChanges:=False
Next
End Sub
Dave Peterson wrote in message ...
Change this line:

wkbk.Worksheets(1).Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)

to

wkbk.Worksheets.Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)



ian123 wrote:

Thanks very much, i can now get it to bring the first sheet of each book
in the range selected. Is it possible to bring all the sheets from
each of the books selected into the new book?

Once again thanks very much for your help on this - it is very much
appreciated

---
Message posted from http://www.ExcelForum.com/