combining spreadsheets

[T]

I have 100 separate spreadsheets. I need to get one
column out of each of the spreadsheets (the same column
in each of the spreadsheets) and combine them into a new
spreadsheet. Does anyone know the solution? Thanks in
advance for your help.

 

[Lydya]

Hi

Create a new workbook, ALT F11 to open Visual Basic Editor, Insert / Module
and copy/paste the following macro:
--
Sub CopyCol()
Dim Directory As String
Dim MainSht As Worksheet
Dim Col As Range
Dim c As Integer
Dim i As Integer
Directory = "C:\TEMP\Excel"
Set MainSht = ActiveWorkbook.Sheets(1)
c = 1
Application.ScreenUpdating = False
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = Directory
.FileName = "*.*"
.SearchSubFolders = False
.Execute
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open FileName:=.FoundFiles(i)
Set Col = Sheets(1).Columns(2)
Col.Copy Destination:=MainSht.Columns(c)
c = c + 1
ActiveWorkbook.Close savechanges:=False
Next i
Else
MsgBox "No workbook was found!"
End If
End With
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
--
This macro will open every Excel file located in the mentioned Directory,
copy column 2 of sheet 1 to sheet 1 of the new workbook. (adapt Directory,
Sheets & Column index to your needs)
If your want the macro to search the subfolders as well, just change this:
..SearchSubFolders = True

Hope it helps!
Lydya