Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets into one
I have 4 files with hundreds of worksheets in each file. I need to combine
all 700,000 rows of data from all the worksheets in these 4 files into one worksheet to take into Access to append additional data. Is there a way to do this without cutting and pasting the data from each individual worksheet into the single list? I'm using Office 07 and although the data is the same format in each of the worksheets the entries vary in the number of rows from sheet to sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets into one
Mark
This macro will do what you want. The following conditions must be met for this to work. The 4 workbooks must all be in one folder. The workbook into which you want the 4 workbooks combined must contain this macro and must also be in that same folder. The 4 workbooks' names must all have an extension of ".xlsx". The workbook that contains this macro must have an extension of ".xlsm". No other workbooks with the ".xlsx" extension should be in this same folder. This macro loops through the 4 workbooks, and in each workbook loops through all the sheets, and copies all the data from A2 down and 10 columns wide and pastes it into the workbook that holds this macro. Make changes to the code as needed to fit with your data. Come back if you need more. HTH Otto Sub AllFolderFiles() Dim wb As Workbook, wbMaster As Workbook Dim TheFile As String, MyPath As String Dim ws As Worksheet, Dest As Range Set Dest = Range("A2") Set wbMaster = ThisWorkbook MyPath = ThisWorkbook.Path ChDir MyPath TheFile = Dir("*.xlsx") Do While TheFile < "" If TheFile < wbMaster.Name Then Set wb = Workbooks.Open(MyPath & "\" & TheFile) For Each ws In wb.Worksheets With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Copy Dest End With Set Dest = wbMaster.Range("A" & Rows.Count).End(xlUp).Offset(1) Next ws wb.Close End If TheFile = Dir Loop End Sub "Mark712" wrote in message ... I have 4 files with hundreds of worksheets in each file. I need to combine all 700,000 rows of data from all the worksheets in these 4 files into one worksheet to take into Access to append additional data. Is there a way to do this without cutting and pasting the data from each individual worksheet into the single list? I'm using Office 07 and although the data is the same format in each of the worksheets the entries vary in the number of rows from sheet to sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets into one
Otto,
Thank you for the macro, this will save an incredible amount of time. I'll try it tomorrow. Mark "Otto Moehrbach" wrote: Mark This macro will do what you want. The following conditions must be met for this to work. The 4 workbooks must all be in one folder. The workbook into which you want the 4 workbooks combined must contain this macro and must also be in that same folder. The 4 workbooks' names must all have an extension of ".xlsx". The workbook that contains this macro must have an extension of ".xlsm". No other workbooks with the ".xlsx" extension should be in this same folder. This macro loops through the 4 workbooks, and in each workbook loops through all the sheets, and copies all the data from A2 down and 10 columns wide and pastes it into the workbook that holds this macro. Make changes to the code as needed to fit with your data. Come back if you need more. HTH Otto Sub AllFolderFiles() Dim wb As Workbook, wbMaster As Workbook Dim TheFile As String, MyPath As String Dim ws As Worksheet, Dest As Range Set Dest = Range("A2") Set wbMaster = ThisWorkbook MyPath = ThisWorkbook.Path ChDir MyPath TheFile = Dir("*.xlsx") Do While TheFile < "" If TheFile < wbMaster.Name Then Set wb = Workbooks.Open(MyPath & "\" & TheFile) For Each ws In wb.Worksheets With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Copy Dest End With Set Dest = wbMaster.Range("A" & Rows.Count).End(xlUp).Offset(1) Next ws wb.Close End If TheFile = Dir Loop End Sub "Mark712" wrote in message ... I have 4 files with hundreds of worksheets in each file. I need to combine all 700,000 rows of data from all the worksheets in these 4 files into one worksheet to take into Access to append additional data. Is there a way to do this without cutting and pasting the data from each individual worksheet into the single list? I'm using Office 07 and although the data is the same format in each of the worksheets the entries vary in the number of rows from sheet to sheet. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets into one
Otto,
On the line, .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Copy Dest, I'm getting an error message that says "Copy Method of Range Calss failed". Any ides what would cause that? Mark "Otto Moehrbach" wrote: Mark This macro will do what you want. The following conditions must be met for this to work. The 4 workbooks must all be in one folder. The workbook into which you want the 4 workbooks combined must contain this macro and must also be in that same folder. The 4 workbooks' names must all have an extension of ".xlsx". The workbook that contains this macro must have an extension of ".xlsm". No other workbooks with the ".xlsx" extension should be in this same folder. This macro loops through the 4 workbooks, and in each workbook loops through all the sheets, and copies all the data from A2 down and 10 columns wide and pastes it into the workbook that holds this macro. Make changes to the code as needed to fit with your data. Come back if you need more. HTH Otto Sub AllFolderFiles() Dim wb As Workbook, wbMaster As Workbook Dim TheFile As String, MyPath As String Dim ws As Worksheet, Dest As Range Set Dest = Range("A2") Set wbMaster = ThisWorkbook MyPath = ThisWorkbook.Path ChDir MyPath TheFile = Dir("*.xlsx") Do While TheFile < "" If TheFile < wbMaster.Name Then Set wb = Workbooks.Open(MyPath & "\" & TheFile) For Each ws In wb.Worksheets With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Copy Dest End With Set Dest = wbMaster.Range("A" & Rows.Count).End(xlUp).Offset(1) Next ws wb.Close End If TheFile = Dir Loop End Sub "Mark712" wrote in message ... I have 4 files with hundreds of worksheets in each file. I need to combine all 700,000 rows of data from all the worksheets in these 4 files into one worksheet to take into Access to append additional data. Is there a way to do this without cutting and pasting the data from each individual worksheet into the single list? I'm using Office 07 and although the data is the same format in each of the worksheets the entries vary in the number of rows from sheet to sheet. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining data from multiple worksheets into one | Excel Worksheet Functions | |||
combining data multiple worksheets into one? | Excel Discussion (Misc queries) | |||
Combining data from multiple worksheets into master worksheet | Excel Worksheet Functions | |||
Combining data from multiple worksheets. | Excel Discussion (Misc queries) | |||
Combining data from multiple worksheets and separate spreadsheets | Excel Discussion (Misc queries) |