Looks like 2 columns to me.

Change this

sh.Columns(1).Resize(, 2).Copy

to

sh.Columns(2).Copy


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Darin Kramer" wrote in message
...
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 ***