Changing code to Consolidate ROWS instead of columns
I have code below which neatly consolidates columns from a worksheet into a
master worksheet. (it opens each workbook within a folder, selects the applicable sheet(Analysis), and copies the applicable columns into the book from which the macro is run, then repeats the process ie opening the next workbook copying columns from the applicable sheet, pasting in the book from which the macro is run in the same sheet in the next free column, and so on) All Im wanting to do is to instead of make it take columns, make it take rows .... (ideally the last row with text in it, or if finding the last row is too difficult, then it could be the range a1:ae300) If you can help, it would be MOST MOST appreciated!!! Regards Darin Code is: 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\dk\Desktop\Consolidation_AR_test_files\*. xls") Do While sName < "" Set bk = Workbooks.Open("D:\Documents and Settings\dk\Desktop\Consolidation_AR_test_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 ' close the workbook bk.Close SaveChanges:=False sName = Dir() Loop ActiveSheet.Select ActiveSheet.Name = "Consol_AR_summary" end sub |
Changing code to Consolidate ROWS instead of columns
Sub ConsolidatRows()
Dim sName As String Dim sh As Worksheet , sPath as String Dim dest As Range, bk As Workbook Dim rng as Range sPath = "D:\Documents and Settings\dk" _ "\Desktop\Consolidation_AR_test_files\" sName = Dir(sPath & "*.xls") Do While sName < "" Set bk = Workbooks.Open(sPath & sName) Set sh = bk.Worksheets("Analysis") Set dest = ThisWorkbook.Worksheets(1).Cells(rows.count,1).end (xlup)(2) set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup)) rng.EntireRow.copy dest.PasteSpecial xlValues dest.PasteSpecial xlFormats bk.Close SaveChanges:=False sName = Dir() Loop ActiveSheet.Select ThisWorkbook.Worksheets(1).Name = "Consol_AR_summary" end sub -- Regards, Tom Ogilvy "djk" wrote: I have code below which neatly consolidates columns from a worksheet into a master worksheet. (it opens each workbook within a folder, selects the applicable sheet(Analysis), and copies the applicable columns into the book from which the macro is run, then repeats the process ie opening the next workbook copying columns from the applicable sheet, pasting in the book from which the macro is run in the same sheet in the next free column, and so on) All Im wanting to do is to instead of make it take columns, make it take rows .... (ideally the last row with text in it, or if finding the last row is too difficult, then it could be the range a1:ae300) If you can help, it would be MOST MOST appreciated!!! Regards Darin Code is: 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\dk\Desktop\Consolidation_AR_test_files\*. xls") Do While sName < "" Set bk = Workbooks.Open("D:\Documents and Settings\dk\Desktop\Consolidation_AR_test_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 ' close the workbook bk.Close SaveChanges:=False sName = Dir() Loop ActiveSheet.Select ActiveSheet.Name = "Consol_AR_summary" end sub |
All times are GMT +1. The time now is 08:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com