Guys,

The VB below is run from an open book (say Master), and it opens all
books within a sub-directory and then:
STEP a) from the just opened workbook selects worksheet ABC and copies
columns 1,2,3 into a first sheet of MASTER
STEP b) from the just opened workbook selects worksheet DEF and copies
ONLY column 3 into the SECOND sheet of MASTER

It then closes the book and repeats, by opening next book in
subdirectory. The second time around tt sucessfully achieves step A
above, (and pastes the next three columns next to the previous three
columns) BUT PROBLEM IS when it does Step B it leaves a gap of two
columns before pasting the once column of data. I want it to paste the
one column next to the previous column,not a leave a gap of 2 columns
(which I think it is getting from step A)

Can anyone help........????? ( i get lost with defining all the i and
the i + 1)

Thanks

D


Sub consolidator
Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook, bk1 As Workbook
Dim sh1 As Worksheet
Set bk1 = ThisWorkbook
i = 1
sName = Dir("D:\...\Consolidation Test Files\*.xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\...\Consolidation Test Files\" & sName)

'first do ABC (STEP A)
Set sh = bk.Worksheets("ABC")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 3
sh.Columns(2).Resize(, 3).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName


' Then do DEF (STEP B)
Set sh = bk.Worksheets("DEF")
Set dest = ThisWorkbook.Worksheets(2).Cells(1, i)
i = i
sh.Columns(3).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName

bk.Close SaveChanges:=False
sName = Dir()
Loop

End sub

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