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. 
 
0
Utf
1/24/2010 4:16:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
729 Views

Similar Articles

[PageSpeed] 54

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" <Mark712@discussions.microsoft.com> wrote in message 
news:543FB279-B6F7-48FF-ABF9-585BC46E4AF5@microsoft.com...
> 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.
> 
0
Otto
1/24/2010 4:29:21 PM
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" <Mark712@discussions.microsoft.com> wrote in message 
> news:543FB279-B6F7-48FF-ABF9-585BC46E4AF5@microsoft.com...
> > 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.
> > 
> .
> 
0
Utf
1/25/2010 1:39:01 AM
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" <Mark712@discussions.microsoft.com> wrote in message 
> news:543FB279-B6F7-48FF-ABF9-585BC46E4AF5@microsoft.com...
> > 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.
> > 
> .
> 
0
Utf
1/25/2010 11:59:01 PM
Mark
    I set up some files in the same folder as the file with the macro and it 
runs fine except for this line:
Set Dest = wbMaster.Range("A" & Rows.Count).End(xlUp).Offset(1)
Change it to:
Set Dest = wbMaster.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
Now to your problem.  Did the macro do ANY copying at all?  If not, then you 
got the error in the first loop.
If you wish, make copies of all 5 of your files and delete most of your data 
& sheets in those 5 copies so the whole mass will be smaller.  I need jut a 
couple of sheets in each file and a dozen rows of data in each sheet.  Run 
the macro and confirm that you still get that error.  Then send me those 5 
files and I'll try to figure it out for you.  My email is 
moehrbachoextra@bellsouth.net.  Remove the "extra" from this email address. 
HTH  Otto
"Mark712" <Mark712@discussions.microsoft.com> wrote in message 
news:708C74F8-C045-4E18-A504-C2F1FA27D70A@microsoft.com...
> 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" <Mark712@discussions.microsoft.com> wrote in message
>> news:543FB279-B6F7-48FF-ABF9-585BC46E4AF5@microsoft.com...
>> > 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.
>> >
>> .
>> 
0
Otto
1/27/2010 7:36:04 PM
Reply:

Similar Artilces:

Creating a Combination Bar Chart with 2 Lines
I am trying to create a bar chart with 2 line series. The Y-axis for the vertical bars would be on the left and the Y-axis for the 2 line series would be on the right (3 series were defined). The horizontal axis for all series are calendar months (Jan, Feb, etc.) Every time I setup the chart my chart only displays a single line, ignoring the other line series. The vertical bars come out fine. Any thoughts on what I am doing wrong? Thanks Bill Falzone Don't use the built in types. Make the chart using all columns. Select a series that you want to change (click on it right in th...

Hidden data when pasting from html page into Excel?
I have a situation where I copy a report, which is generated as an html page, into Excel for analysis. When a new copy/version of the report is available, I copy and paste the new data over top of the old in Excel. I've noticed that the size of my Excel file is growing after each copy/paste situation. To manage this, I can delete the worksheet where I paste the data and reconstruct it. After doing nothing else, saving the workbook results in an immediate reduction of file size. If I simply select everything on the sheet and delete, the file size does NOT go down; I have to delet...

Algorithm to combine identical items in a list
I have a rather large worksheet that contains a list of all items in a warehouse inventory. I would like to be able to combine duplicate items as new inventory is added. Each item has (among other fields) an item ID, a color and a condition. I would like to combine only the lines where all of these fields are identical, summing the quantities in each record, and recalculating the cost based on the cost field in each record. Currently, I do this by starting at the top of the list and comparing each record to all of the records below it to the end of the list. This is rather time-cons...

Outlook 2002 and Retrieving Data Error Message
OS is Windows XP SP1 Email - Outlook 2002 SP3 Error Message: Outlook is retrieving data from the Microsoft Exchange Server <server name>. You can cancel the request or minimize this message to the Windows taskbar until Outlook closes the message automatically. Other Related Issues: At times, the message displays the correct server name and sometime it does not. At times, it displays the name of an active directory server. This only happens to workstations located in an remote office connecting to the exchange server via ADSL\VPN connection. We have one exchange 2003 server - public...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Search Multiple Worksheets #2
Is there a way to search trough multiple worksheets for a specific value? Other posts have mentioned to use VBA, but I have never used that before. If anyone can give me some advice on using that or a type of formula to perform that can search multiple worksheets. Thank You There may be other ways but, while holding down the ctrl key select each of the worksheet tabs you want to search in then select Edit|Find from the menu. Type in the value you want and it will go to the first instance of that value. Now if you are wanting to preserve a specifice value for reference or ???, then...

Add the same field twice to a pivot table but filter one of them?
In my datasheet, I have a "cost" column and a "date" column so each cost has an associated date. In my pivot table, I've added the "cost" as a field, which shows the total and this is fine. However, I'd like to add the "cost" as a field again and this time selecting which dates to include in the cost number so that I have two cost fields side by side. Is something like this possible? Hi That is not possible in the same PT. You will need to set up a second PT based upon the same data set as the first but do NOT use the same Pivot Cache to save ...

map data (x-y axis) with diffent series name
I have a set of x-y coordinate data set and i would like to plot them showing the respective series name. Is there an automatic way of doing this without haing to plot the data points separately. eg Name x y A 1 6 B 2 3 C 3 4 D 4 2 E 5 5 .. . . .. . . .. . . I will like to have a plot of x and y with A, B, C as series name without having to plot the points one after the other. Hi, Here are a couple of free addins that allow you to link data labels to cells. Rob Bovey's Char...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

opening excel worksheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel whenever I try to open an existing excel worksheet it opens very short. I have to drag it down to the bottom of the screen, is there a setting that i need to adjust? No, simply SAVE the workbook after you adjust it. Next time, it will open at the size you want. On 27/03/10 6:42 AM, in article 59bb6065.-1@webcrossing.JaKIaxP2ac0, "scrapper@officeformac.com" <scrapper@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel > wh...

Multiple AND OR functions
Is it possible to make this function work? =IF(AND(AND($B9="Z",AE9=35),OR(J9=1,J9="M",J9="C")),1000,IF(AND(AND($B9="A+",AE9=35),OR(J9=1,J9="M",J9="C")),750)) I need to find out if Cell B9 = Z and if Cell AE9 = 35, if this true then check cell J9 and check if it equals 1, M or C then return the value as 1000. (that bit works OK) I also want it to check if an alternative statement is true if the first is false whereby it checks the the same set of cells but this time, check if B9 =A+, if Cell AE9 = 35, if this true then...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

Offline Address Book, Multiple Administrative Groups
Hello, We recently deployed an additional Exchange server in our organization and placed it into its own administrative group, now users on the new server are getting 8004010F errors in Outlook when attempting to download the offline addresss book. The users who are on the original server do not receive these errrors when downloading the address book. If anyone can provide some assitance it would be greatly appreciated. It's look like this http://support.microsoft.com/default.aspx?scid=kb;en-us;162703 "jballin" <jballin@discussions.microsoft.com> wrote in message...

Multiple copies of E-Mail messages
I am using Outlook 2002 (10.4219.4219) SP2 with a Windows XP Professional operating system. Just in the last couple days I've started to experience a problem with incoming e-mail messages. I use Outlook to retrieve e-mail from at least four different accounts, from at least two different servers. When I receive a new e-mail message that is addressed to one of these e-mail accounts, I get two extra copies of that message, and each of these extra copies are addressed to two of my other accounts. So what I end up with is three copies with three different TO: addresses. This only happens ...

Outlook is trying to retrieve data from the Microsoft Exchange Ser
Hi I keep getting a msg saying "Outlook is trying to retrieve data from the Microsoft Exchange" everytime i try and send a mail... the mail then gets stuck in my outbox. I still receive all email and this only happends over one particular broadband connection??? If i use dial up it works and if i go to some other broadband connection it works... any ideas? _VERITAS_ wrote: > Hi > I keep getting a msg saying "Outlook is trying to retrieve data from > the Microsoft Exchange" everytime i try and send a mail... the mail > then gets stuck in my outbox. I s...

Macro to generate 120 Graphs and put them into ONE sheet
The attached files has 120+ items that need to graphed against the date I have tried to write a script that would automatically graph but ca not: 1) Get the For loop to generate multiple graphs 2) figure out how to arrange the grpahs in ONE sheet neatly Any help is greatly appreciated. Thank you !!! Shankar -- Message posted from http://www.ExcelForum.com sorry - here is the fil Attachment filename: tester.xls Download attachment: http://www.excelforum.com/attachment.php?postid=66758 -- Message posted from http://www.ExcelForum.com If you don't n...

how do i switch to a right to left worksheet
Do you mean this ? http://www.rondebruin.nl/sheetdirection.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "li" <li@discussions.microsoft.com> wrote in message news:C02DC0F7-14E2-4424-8A62-8191E853C48B@microsoft.com... > under Excel Options -- Regards Dave Hawley www.ozgrid.com "li" <li@discussions.microsoft.com> wrote in message news:C02DC0F7-14E2-4424-8A62-8191E853C48B@microsoft.com... > ...

Saving data #2
Hi all, I need to save data (results) from a base spread sheet program that i use on a weekly basis. i am in the middle of building this program, and have just discovered macros, but this, along with links is about my current knowledge of excel how can i automate to accumulate data from the base spread sheet (program) when i clear all data from the program to produce fresh results the following week, and to keep past data up to date and available for further use. Any help would be appreciated. legepe With a combination of formulas and dynamic named ranges, it is possible to just add the ...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

Multiple SMTP Address
If a user is set up to have multiple SMTP address setup in active directory. When they send an email in outlook how can they chose which one it is from. You can only do this, in the setup you describe, by using a 3rd party app such as ChooseFrom from www.ivasoft.biz -- Mark Arnold. "Shane" wrote: > If a user is set up to have multiple SMTP address setup in active directory. > When they send an email in outlook how can they chose which one it is from. On Tue, 31 May 2005 06:41:03 -0700, "Shane" <Shane@discussions.microsoft.com> wrote: >If a user is s...

Printing a worksheet in two (or more) columns
Hi, I've got an extremely long spreadsheet table comprising two columns of data. I'd like to print these data in a more compact form - in the same way that the entries are organised in a telephone directory. Say on average my page is 50 rows high - my first two columns on page 1 would appear on the left hand side and be rows 1 through 50, row 51 would continue at the top of the page, but on the right-hand side. Row 101 would appear at the top left hand side of page 2, etc. I'd like to keep the orientation of the columns in portrait (I did find a printer-associ...

Get Access Data into Excel
Hi All, I am using excel macro to get data from access database. My sql query gives me 5 records or more than that. I am able to pull it different cells. But I want all the 5 results in single cell. Please help im not exactly sure what your trying to do, but maybe something like this will do the trick dim accval as string accval=rs!:XXX rs.movenext accval=accval & " " & rs!:xxx loop it till rs.eof=true hth dmoney "fi.or.jp.de" wrote: > Hi All, > > I am using excel macro to get data from access database. >...

Cut'n'Pasting data
Greetings ! I have a CSV data file wot looks something like this - "1529.17698720957","133.597550559965" "1685.21901149326","132.817184396522" "1900.00000000000","130.300887834893" "2193.34485206410","121.730777157435" "2363.17295960769","114.937652855693" "2523.01169878763","108.544103288496" "3240.77088467590","87.805735336415" "3590.69860622591","81.680775462264" "4229.19543928027","78.487652800160" This data i...