Open book, copy and paste from sheet, and then close....

[Darin Kramer]

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

 

[Morris]

I'd do it this way:

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, ThisWorkbook.Worksheets(1).usedrange.columns.count)
'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, ThisWorkbook.Worksheets(1).usedrange.columns.count)
'i = i
sh.Columns(3).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName

So actually I'd change i to the present's worksheet's
usedrange.columns.count

And btw i = i doesn't do anything

let me know if it works.
Morris

 

[Darin Kramer]

Nope - doesnt work - now only pastes one column (instead of two) and
puts that one column in column DG for some strange reason??

 

[Morris]

Nope - doesnt work - now only pastes one column (instead of two) and
puts that one column in column DG for some strange reason??

Then create a seconder counter - j as integer and do step B like this:

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

dont forget to assign a start value for j on top of the code. j = 1

 

[Darin Kramer]

I get erro message duplicate declaraion in current scope - im no expert
- I said Dim j as integer...?

 

[Morris]

I get erro message duplicate declaraion in current scope - im no expert
- I said Dim j as integer...?

check the code for double declaration of:
dim j as integer
maybe you put it once on top of the Sub and second tim e somewhere in
the step B text block?