Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating sheets | Excel Discussion (Misc queries) | |||
Consolidating all sheets | Excel Discussion (Misc queries) | |||
Consolidating data from 4 sheets to one! | Excel Worksheet Functions | |||
Consolidating sheets | Excel Worksheet Functions | |||
Consolidating multiple sheets | Excel Discussion (Misc queries) |