Hi there,

The code below correctly opens however many excel workbooks located
within a sub directory, and copies and pastes the first 4 columns into
another sheet. (end result is you have consolidated the 4 columns into
new sheet)

QUESTION I
I want to modify the code ONLY to copy column 2 into the new sheet, (and
do repetively for all books within the same sub directory) How do I do
so...? (My VB skills are slightly limited.., so not sure which variable
to change....)

Question II
Is there any way to turn off the auto alert when you open a book that
says "Do you want to enable Macros", and the auto alert when you close
the book that says "there is a large amount of data on the clipboard, do
you want it available for later use...?

Thanks!!!!
Regards

Darin

Sub Consolidator

Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook
i = 1
sName = Dir("D:\Documents and
Settings\user\Desktop\Projects\Projects_06\Consoli dation_test\results\*.
xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Documents and
Settings\user\Desktop\Projects\Projects_06\Consoli dation_test\results\"
& sName)
Set sh = bk.Worksheets("Answers")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 1
sh.Columns(1).Resize(, 2).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName
' close the workbook
bk.Close SaveChanges:=False
sName = Dir()
Loop
ActiveSheet.Select
ActiveSheet.Name = "Consolidated"



End Sub

*** Sent via Developersdex http://www.developersdex.com ***