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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to code to consolidate two sheets...
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:\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 set sh1 = Bk1.Worksheets.Add(After:=bk1.Worksheets(bk1.works heets.count)) sh1.name = left(sName,len(sName)-4) bk.Worksheets("ABC").Range("A1:C1").EntireColumn.c opy sh1.Range("A1:C1").PasteSpecial xlValues sh1.Range("A1:C1").PasteSpecial xlFormats ' close the workbook bk.Close SaveChanges:=False sName = Dir() Loop ActiveSheet.Select ActiveSheet.Name = "Consol" -- Regards, Tom Ogilvy "Darin Kramer" wrote: 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to code to consolidate two sheets...
Thanks Tom... looking good..., but I got a run time error which says
"While renaming a sheet you entered an invalid name" refers me to line : sh1.Name = Left(sName, Len(sName) - 4) Any ideas... ? (Im not sure that the name of the tab has to be the same as the sheet which it is opening - ie it can be any name or any sheet, as long as its not the same as the first sheet.. Kind Regards Darin *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to code to consolidate two sheets...
Kinda got it to work by ignoring error line of code, BUT also, I need
the result to be copied to the SAME sheet each time, ie NOT to a new sheet, ie move one column along and then paste the result, move one column along and paste the result, etc etc Regards Darin *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to code to consolidate two sheets...
Should be easy to modify. Move the add to before the loop
keep track of what column you are copying to. Increment after copying. -- regards, Tom Ogilvy "Darin Kramer" wrote in message ... Kinda got it to work by ignoring error line of code, BUT also, I need the result to be copied to the SAME sheet each time, ie NOT to a new sheet, ie move one column along and then paste the result, move one column along and paste the result, etc etc Regards Darin *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to code to consolidate two sheets...
Tom, Im by no means a VB expert, so am struggling a bit - according to me the add is before the loop already, and I dont know what the syntax is for an increment... Im just trying to get the second set all to appear in one sheet...:) Appreciate your continued efforts!!! Regards Darin *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
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 |