Dear microsoft.public.excel.programming,
I have about 50 files each with 30 sheets. Files are all similarly named
e.g. File112008-01, File122008-01 etc. and all sheets are named as dates
(e.g. first sheet is 11.01.2008, second is 11.02.2008 etc.), I'd like to
copy all E columns from all the sheets from all the files on one sheet,
column by column (after all place is taken, that it goes to another sheet).
How can I easily solve this? (I recently started to learn how to solve
easier problems with VBA but one this solution are some other problems I
have based, and I couldn't find myself how to solve it). I hope it's not too
much a newbish question in this group.
Thank you in advance

change Folder as necessary

Sub Getfiles()


Folder = "C:\temp\"
Set DestSht = ThisWorkbook.Sheets("Sheet1")


FName = Dir(Folder & "*.xls")
Do While FName < ""
Set Bk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In Bk.Sheets
LastRow = Sht.Range("E" & Rows.Count).End(xlUp).Row

If DestSht.Range("A1") = "" Then
NewRow = 1
Else
NewRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row + 1
End If

DestSht.Range("A" & NewRow & ":A" & (NewRow + LastRow - 1)) = _
FName

DestSht.Range("B" & NewRow & ":B" & (NewRow + LastRow - 1)) = _
Sht.Name

Sht.Range("E1:E" & LastRow).Copy _
Destination:=DestSht.Range("C" & NewRow)

Next Sht
Bk.Close savechanges:=False
FName = Dir()
Loop

End Sub


"small caps" wrote:

Dear microsoft.public.excel.programming,
I have about 50 files each with 30 sheets. Files are all similarly named
e.g. File112008-01, File122008-01 etc. and all sheets are named as dates
(e.g. first sheet is 11.01.2008, second is 11.02.2008 etc.), I'd like to
copy all E columns from all the sheets from all the files on one sheet,
column by column (after all place is taken, that it goes to another sheet).
How can I easily solve this? (I recently started to learn how to solve
easier problems with VBA but one this solution are some other problems I
have based, and I couldn't find myself how to solve it). I hope it's not too
much a newbish question in this group.
Thank you in advance

When you have some free time you may want to take a look at this:
http://www.rondebruin.nl/csv.htm

Excellent Batch Processing example he
http://www.rondebruin.nl/copy4.htm

Regards,
Ryan---

--
RyGuy


"Joel" wrote:

change Folder as necessary

Sub Getfiles()


Folder = "C:\temp\"
Set DestSht = ThisWorkbook.Sheets("Sheet1")


FName = Dir(Folder & "*.xls")
Do While FName < ""
Set Bk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In Bk.Sheets
LastRow = Sht.Range("E" & Rows.Count).End(xlUp).Row

If DestSht.Range("A1") = "" Then
NewRow = 1
Else
NewRow = DestSht.Range("A" & Rows.Count).End(xlUp).Row + 1
End If

DestSht.Range("A" & NewRow & ":A" & (NewRow + LastRow - 1)) = _
FName

DestSht.Range("B" & NewRow & ":B" & (NewRow + LastRow - 1)) = _
Sht.Name

Sht.Range("E1:E" & LastRow).Copy _
Destination:=DestSht.Range("C" & NewRow)

Next Sht
Bk.Close savechanges:=False
FName = Dir()
Loop

End Sub


"small caps" wrote:

Dear microsoft.public.excel.programming,
I have about 50 files each with 30 sheets. Files are all similarly named
e.g. File112008-01, File122008-01 etc. and all sheets are named as dates
(e.g. first sheet is 11.01.2008, second is 11.02.2008 etc.), I'd like to
copy all E columns from all the sheets from all the files on one sheet,
column by column (after all place is taken, that it goes to another sheet).
How can I easily solve this? (I recently started to learn how to solve
easier problems with VBA but one this solution are some other problems I
have based, and I couldn't find myself how to solve it). I hope it's not too
much a newbish question in this group.
Thank you in advance