Hi There,

The code below neatly consolidates a specific sheet from a workbook
within a sub-directory into a sheet of the book from which the Macro is
run.

I would like to modify it to in addition to selecting column 2 from the
"analysis" sheet, to also select 3 columns from sheet "abc" and paste
into a NEW sheet (ie not the same sheet that existing macro refers too.

(I could just duplicate the macro, changing the sheet name, but then I
would be forced to open all the workbooks twice, so thats why Im trying
to do it all within one Macro...)

Its a complicated one, and thus I appreciate the help even more!!! Any
ideas...?

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_AR_test_files\*
..xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Documents and
Settings\user\Desktop\Projects\Projects_06\Consoli dation_AR_test_files\"
& sName)
Call Sheets_Anaylsis_very_visible
Set sh = bk.Worksheets("Analysis")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 1
sh.Columns(3).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 = "Consol"





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