Consolidating several sheets into one.
 

HOwdie,

Through help from this forum :), the code almost works.

The code below consolidates two sets of information -
1) From a sheet called analysis into a new blank sheet
2) From a sheet called summary_stats into SEVERAL sheets - this is the
problem I want to consolidate the second set of information into a
second sheet - ie NOT into several sheets, NOR into sheet 1 above, but
into a new second sheet

Think the problem is from after code starting with Set sh1 =
bk1.Worksheets.Add(After:=bk1.Worksheets(bk1.Works heets.Count))

Appreciate any assistance, as Im so close, but yet so far...

Regards

Darin

Sub consolidator2()
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:\Projects_06\Consolidation_AR_test_files\*. xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Projects_06\Consolidation_AR_te st_files\"
& sName)
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
Set sh1 =
bk1.Worksheets.Add(After:=bk1.Worksheets(bk1.Works heets.Count))

bk.Worksheets("summary_stats").Range("d1:d1").Enti reColumn.Copy
sh1.Range("a1:a1").PasteSpecial xlValues
sh1.Range("a1:a1").PasteSpecial xlFormats
' close the workbook

bk.Close SaveChanges:=False
sName = Dir()
Loop
ActiveSheet.Select
ActiveSheet.Name = "x"

End Sub

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