Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to code to consolidate two sheets...
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 *** |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modification to this code | Excel Discussion (Misc queries) | |||
Code modification help | Excel Worksheet Functions | |||
modification for the code | Excel Programming | |||
Code Modification | Excel Programming | |||
Modification to code | Excel Programming |