Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Collate Many Workbooks Into 1

we have 1500 workbooks where the format is the same but the data could be different. I have marked up in red which i would class as headings, the rest in black are varibles

sheet 2 is how i would like it if possible. please note the file name is need to be in column A

can a macro be written to get all 1500 files into one. all the files are in one folder

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Collate Unique Cells From Many Workbooks
I have approx 850 workbooks with different names. In each workbook there are unique cells that I need to collate into rows in another separate workbook. For example:

Workbook named ABC123 - on the sheet named �cover sheet� I need to record the information from cell�s A1, C3, D3, C37 and D37. Into another workbook called record book. I need each row in the record book to unique to the individual workbooks. For example information from ABC123 will be recorded in row 2 of the record book while information from ABC456 will be recorded on row 3 and so forth.

I have been opening each book and copying and pasting each cell (my fingers are killing me) into the record book. Just thought somebody may know another way. Not familiar with vba or even how to insert it into excel.

View Replies!   View Related
Collate Worksheets From Multiple Workbooks Into One
I have a few hundred multi sheet workbooks that have address data in one of the sheets that I wish to collate into a single worksheet to use as a mail merge with word.

View Replies!   View Related
Collate Data
I am looking to collate Data of Peoples Activities from their status and duration. I would like to have a table where I would be able to see each agent name, their status and how long they took in each one. e.g.

Name | Status | Total Time
Humpty Dumpty | NotReady | 0:15
| Wait | 0:12

I have attached a sample worksheet of data, if anyone can guide me as to how i can do this also if this can be done through a macro, that would be alot easier as i have to collate atleast 200 peoples activities.

View Replies!   View Related
Collate Data From Different Files
I have multiple excel files with a form popping up whenever these files are opened (form has been called in Workbook open function). I need to collate data from these multiple excel files without opening these files. Can somebody help with a sample code?

View Replies!   View Related
Collate A Range Of Data
I developed a database for tracking our Search and Rescue activities on an annual basis; this usually involves ~140 calls per year. Amongst other things, we log the time the call came in. The time is entered in numbers format, not text, as I couldn't figure out how to get a time range (09:30, etc.) to work. It's like military time...0015, 0945, 1730, etc., so in numbers format, 0930 comes out as "930", 0030 as "30", etc. I developed a tracking column in one hour increments...0000-0059, 0100-0159, etc. Each time a call is entered and the time logged, I'd like to keep a sum total down in the time range a call received at 0930 would end up in the 0900-0959 box. Among other things, I tried formatting with, "=COUNTIF (range:range, ">0900, <0959") but that doesn't work.

View Replies!   View Related
Collate Information From All Files In Folder
I have created an Excel Spreadsheet to capture essential information pertaining to a project within a programme. I have used Excel 2003 and protected the sheet with the password Secret and the file can be downloaded here: [url]

I now have a number (30 or 40) of these filled out for each project.
I now need to be able to extract the data, specifically the resource profile. I want to be able to run a macro that will open each spreadsheet in a directory and extract the data and populate the spreadsheet Collated Output which can be found here: [url]

The data needs to be appended into the table but ignore empty rows. I created the template in such a way that each section has a static range of cell references.
The Template allows the user to change the start month of the resource profile so that needs to be taken into account when extracting the data so that the profile is aligned to the correct month.

View Replies!   View Related
Sumif Function To Find And Collate Unqiue Data On Different Worksheets
Most excel/vba books say not to use the sumif function.
I have a very large workbook (9meg) and cant use VBA code as it slows down the interation calculations required.
I have used the sumif function to find and collate unqiue data on different worksheets.
Is there another function that i should be using?

View Replies!   View Related
Exclamation Collate Data From Varying Number Of Sheets To One Sheet
Have some code I have found from this site shown below. It works great however I need some help in editing it to change what it does. Basically the code currently takes all the data from set cells from all sheets after a set point and adds a new sheet and copies data from each of the sheets between this point and the new sheet and then pastes it to this final sheet.

What I wish to change is I already have a sheet which I will be using to analyse data from so how would the code look to be able to point to such a sheet within the same workbook? The sheet will be within the first 2 sheets of the workbook so from the code below you can see I have already set it to not include this sheet. I wont be needing a new sheet to be created at the end, I just want the data to paste to my analysis sheet which we can call "analysis".

View Replies!   View Related
Close Workbooks And Move In And Out Of Workbooks In VBA
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.

View Replies!   View Related
Collate All The Sheet In To 1 Sheet
I have 20 sheet in the Excel file. in the below Format.

i want collate all the sheet in to 1 sheet.


View Replies!   View Related
Sum Several Workbooks
I've got a workbook for every department which is used to make a weekly status. Each of these workbooks contains a sheet with the 'weekly statuses over time', i.e. a table showing the weekly status for the department for week 1, week 2, week 3 etc. I've attached the file to make it easier to understand what I'm talking about, see Weekly_Test. Currently I can only see the performance of each department but I would like to be able to see the total performance for all departments, i.e. have a 'Total workbook' where all the weekly statuses are summed up weekly.

In the Weekly_Test file you can see the code which is used to generate the 'weekly status' sheets (also inserted at the end of this post). All workbooks for the departments are the same, apart from the file name which is the name of the department and this does not change. The weekly status is generated in the following way:

In the sheet 'Indtastning' (data entry) you enter the data and the date. Then click 'Update'. The sheet is then copied to a new sheet with the date and all data is copied to the table in the 'his.status' sheet. Now, I would like to add some code so that when I click 'Update' the data which is copied to the table in the 'his.status' sheet is also copied to a similar table in the 'Total' workbook.

Dim indtastArk
Dim arkDato
Public Sub Opdater()
arkDato = Cells(1, 1)

' nulStilIndtastning
End Sub
Private Sub OpretDatoArk()
With ActiveSheet
End With

ActiveWorkbook.Sheets.Add Before:=Sheets(4)......................

View Replies!   View Related
Loop Through Workbooks & Sum
I was just wondering whether VBA in Excel has an equivalent of the VB code

GrabData= Sheets....etc

in VBA and if so, is it possible to use it to grab data from different worksheets, not just sheets?

View Replies!   View Related
Consolidation Of Workbooks
I still have a problem with consolidation of workbooks.


I am working in HQ and at the end of each month, every branch under my care will submit an excel workbook to me. Inside this are two worksheets (worksheet 1 and worksheet 2 for convenience). Both have standard headers and rows and rows of data. Upon receipt, I will keep all these files in a folder called "Oct06" (and Nov06 etc for future mths)


I'm not able to find an easy way to consolidate all the various worksheet 1 into one master worksheet. I need this so as to generate a pivot table. At the same time, I need to consolidate all the worksheet 2 also for the same purpose.

View Replies!   View Related
Merge All Workbooks In One
I have 5 worksheets, representing data from 5 days, M-F. On a weekly basis I open all 5 files and manually copy, and paste all into one large weekly file. Does not take very long so I am probably just being lazy but if I could find a simple solution to merge all 5 into one quickly it would be great.

The one solution I was planning on implementing was to cycle through all OPEN workbooks, copy the data into a new workbook, and close each workbook as the copy and paste is done. This will work but to me its 'dirty'. Each workbook only has one Worksheet.

View Replies!   View Related
Selecting Workbooks ...
I'm not sure why but Excel doesn't seem to like the following.

Workbooks("Bulk Forecast.xls").Worksheets("Bulk Forecast").Range("A1:C2").Copy

View Replies!   View Related
Consolidating Workbooks
I have 85 workbooks in the same folder with a sheet in each workbook called "Budgets". Its the same template in each workbook with different data. I need to create a Summary master file of all my budgets so in the end I will have 1 Workbook with the 85 copies of the "Budgets" Worksheets. Does anyone know the VBA for this?

View Replies!   View Related
CLosed Workbooks
Is it possible to search all closed workbooks in a folder in a range B13:B33 for a particular word?

All closed workbooks have only 1 sheet in them this sheet is always named the same but the workbooks are named sequentially, eg 1, 2, 3 etc.

View Replies!   View Related
Using Buttons Over 2 Workbooks...
I currently have a sheet (SHEET-A) which pulls in a figure from a cell (CELL-G) in another workbook sheet (SHEET-B), and displays the value in CELL-X.

CELL-X then is used for a series of calculations on SHEET-A.

CELL-Y is adjacent to CELL-X and can have a number entered into it which is then used instead of CELL-X for the calculations on SHEET-A. (It is also incidently displayed over CELL-Gs value in CELL-X)

I would like to have a button next to CELL-Y which upon click alters the value of CELL-G in SHEET-B to that of CELL-Y in SHEETA.

View Replies!   View Related
Back And Forth Between Workbooks
Copying information from various sheets from one workbook to paste into similar sheets in another workbook? It would involve switching back and forth between workbooks.

View Replies!   View Related
Switching Between 2 Workbooks
From a workbook, I need to open a further workbook called 'transfer' and then switch back to the original workbook. Presumably I need to save the current workbook name to a variable, open transfer with Workbooks.Open Filename:="c:debworkTRANSFER.xls"
then switch back to the original

How do i pass the original workbook name to a variable, and then reactivate it. I have tried numerous options but just cannot get it to work. Do i reactivate with workbook. activate or windows.activate

View Replies!   View Related
Add-in UDF Works In New Workbooks But Not In Old Ones
I wrote a UDF that does some advanced lookup trick in a particular workbook. That workbook has to be open during this process of course. I used to have a copy of this UDF in several workbooks where I need to use it. This turns out to be bothersome since I needed to update the UDF frequently. So I decided to make an add-in.

I then copied the UDF to a new workbook, save it as an add-in (XLA), then go into Excel Options - Add-in (I use Excel 2007) and enable the add-in there. I then remove the UDF from each and every workbook that used to have it.

Now I close everything.

I then open the lookup workbook that needs to be open if I were to use the UDF. Here is where I don't understand. If I open a new workbook and use the UDF, it works fine. If I save the new workbooks then reopen them, the function still works. However if I open some old workbook where the UDF used to be and try to use the UDF, Excel gives me #Name? errors.

View Replies!   View Related
Formulas Across Two Workbooks
In Workbook 1 O1, I need a formula that will, if Workbook 1 M1 = "Yes", find the value from Workbook 1 N1 anywhere in Workbook 2 Column E, and when it is found, look at the value 2 columns to the right in Workbook 2. If there is a value in this cell, return "Yes" in Workbook 1 O1. If there is no value in this cell, return "No" in Workbook 1 O1. If Workbook 1 M2 = "No", the formula should return "No".

View Replies!   View Related
Open Several Workbooks
I have a macro on a workbook that when I run it, it's supposed to open a workbook, but it opens another workbook also.

How do I prevent this behavior?

View Replies!   View Related
Comparing 2 Workbooks
I have got 2 excel workbooks with multiple worksheets (around 6 megs in size). Is there a way i can do a quick comparison which will point out the differences between the cells?

there is a difference in dize, 200 KB

View Replies!   View Related
Link To Other Workbooks
If you are working in an excel spreadsheet that is linked to other excel files, when you double click on the cell that is linked to another workbook, it immediately opens the linked workbook if your settings are correct. How do you change your settings to enable this functionality?

View Replies!   View Related
Linking Workbooks ...
I run a number of spreadsheets each 4 week period and these are stored in a folders named after the period. ie Period 10 will contain workbookA10wk1, workbookA10wk2, workbookA10wk3 and workbookA10wk4,

Period 11 will contain the same workbooks but named workbookA11wk1 etc.

The data from the workbooks is then used in graphs / tables for each year.

I always pick up the same cell ref in each workbook to compare each week and my table is set out with heading of period and wk.

Is it possible to use the table headings to produce the names of the workbooks that you wish to reference, ie: ='Period 13[workbookA13wk1.xls]Sheet1!$D$17 to appear under Period 13, wk1

View Replies!   View Related
Hlookup In Different Workbooks
I used the following formula when the worksheet was in the same workbook.


The information in cell B14 is the same as the target sheet name.

I want to move the target sheet to another workbook.

How would i need to adjust the formula in order to return the same information from this new workbook?

View Replies!   View Related
Workbooks Count
I have a script running on a worksheet that checks that a name of a workbook is open or not

What happens is, it doesn't recognize that the workbook is open even when it is. When there are 2 workbooks and i msgbox workbooks(2).name, it gives me an error.

Private Sub Worksheet_Activate()

numofbooks = Workbooks.Count
j = 1
Bid1 = Sheets("Bid Summary").Range("B2").Value
For i = 1 To numofbooks
If Workbooks(i).Name = Bid1 Then
j = 0
Goto leave
End If
Next i '

If j = 0 Then
Sheets("Main").Range("L9").Value = "Checked"
Sheets("Main").Range("L9").Value = "Check Failed"
End If

End Sub

View Replies!   View Related
Referencing Workbooks ..
I have this excellent code which I found on here that looks at the data in column A, looks at the data in column B & then returns the differences ie.

John________Jimi_________________Not in A_______Not in B

Here is the code ....

View Replies!   View Related
LOOKUP For 2 Different Workbooks
LOOKUP for 2 different workbooks. I have been using the following codes to do the task.

View Replies!   View Related
Using If Formula Across Different Workbooks
I was just wondering if it was possible to do an If statment that looks at a cell in another workbook?

View Replies!   View Related
Insert Row In Different Workbooks
I have 3 different excel files. Each one of them has one sheet. Column A is identical in all the sheets. The others change. I would like to insert a row, let's say between the second and the third row of the initial sheet of the first file, write something in Column A of the inserted row and excel to update the other files with the inserted row in the right position and with the text in column A. I searched all the conventional ways, but I did not find any solution.

View Replies!   View Related
Emailing Workbooks
I am attaching workbooks which reflect my original work. I have some questions :

1. How can I allow users to view this with everything working fine? (zipping and emailing was suggested, but I am getting some errors)

2. How can I ensure that the viewing format remains same for everyone viewing it?Al colleague of mine, tried and the formatting is wierd for him

View Replies!   View Related
Switching Between Workbooks
I'm trying to write a piece of VBA code which will switch back and forth between 2 workbooks, but I was hoping that I would not have to name both workbooks in my code.

So far I have been able to name the second file as a variable, open it and copy the data I need, but I now need to switch back to the original file. Is there any way of doing this without hard coding the file name?

View Replies!   View Related
Vlookup Between Two Workbooks
I have looked through the forum and haven't been able to find an answer. I have two workbooks. One has a worksheet with two columns - the first column holds ID's, the second column has the names associated with them. My second workbook has a large table with many different fields and ID's only. I would like to use Vlookup to add the names to the second workbook.

View Replies!   View Related
Macro Available To All Workbooks
I would like to create a module for spreadsheet and I want it to be included in all excel files, i.e. whenever you open any excel file that vba module should be included

View Replies!   View Related
UDF #VALUE! When Other Workbooks Open With Same UDF
I have a UDF in a workbook that works fine until I open another workbook which contains the same UDF. When Excel does a full recalculation it not only recalculates the currently selected workbook but also recalculates all other open workbooks. Something from the currently selected workbook appears to interfere with the other open workbooks as these other workbooks show #VALUE! in all cells that use the UDF. Is there some way to make a UDF unique only to the workbook that it resides in such that opening another workbook with the same named UDF won't interfere with it?

Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn)
Dim Code As Range
Dim MonthColumns As Integer
Dim CalcResult As Double
SumRangeLookup = 0
For Each Code In Range(Database)
On Error Goto SkipCode
If Code >= FromCode And Code <= ToCode Then
For MonthColumns = FromColumn To ToColumn
CalcResult = CalcResult + Code.Offset(0, MonthColumns)
Next MonthColumns
End If
Next Code
SumRangeLookup = CalcResult
End Function

View Replies!   View Related
Add Footer In All Workbooks
I want a footer to appear on every Excel workbook that I open. Including the following code in ThisWorkbook of each workbook works, but I want it to work on any workbook, i.e. by including the code in ThisWorkbook of my Personal Workbook (personal.xls)

Private Sub Workbook_Open()
Dim ws As Worksheet
Application. ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing footer in " & ws. Name
With ws.PageSetup
.LeftFooter = "&7Page &P of &N" & Chr(13) _
& "Path : " & ActiveWorkbook.FullName & Chr(13) _
& "Printed on &D at &T"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
End Sub

However, when I include the code in Personal.xls, I receive the following error message when I open an Excel file: Run-time Error '91': Object variable or With block not set

View Replies!   View Related
Macro To Work Across Many Workbooks?
I need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?

View Replies!   View Related
Compare Data With Different Workbooks
i had received a new project. i want to compare the data among 2 different workbooks. there are book1 and book2 which i attach at here. if there are 1 cells is different with both workbooks, then the whole row will copy and paste to a new workbook "output.xls".

i also attach the "output.xls" at here.

the background color i highlighted which data is different, just for easy to view. so can ignore the color.

View Replies!   View Related
Multiple Excel Workbooks Into One
I have a folder where we get 10-15 workbooks and we need to convert these workbooks into one. Currently we manually copy from these individual workbooks and then paste it to the one which we want to have all the data from all the workbooks. This is really time consuming. Can anyone of you help me with creating a Macro which will do the job?

View Replies!   View Related
Opening Workbooks In A Folder
Is there a way to use a for next loop to open a workbook that is in a folder, then save and close the workbook then open the next workbook in the folder and do the same routine till all the workbooks have been updated?

View Replies!   View Related
Fetch Rows From Workbooks
copied some of the code from this website. but still i am struggling with coding. it gives error subscript out of race of my sheet name
what is wrong in it

/tag acccounts = ActiveSheet.Name
TempSh = ActiveSheet.Name
Application.ScreenUpdating = True
For Each f1 In fc
With Sheets("accounts")"/tag

View Replies!   View Related
References By Using Paths To Other Workbooks
is it possible to use references and concatenate them together that they are still references?

i have in:

A1: 'C:Infonic[file2.xls]sheet2'

A1: test

what i need is in sheet2 of file1

the value of file2.xls!sheetY!A1 = test
the problem is that i need to use the path to file2!sheet2 of the cell A1 in sheetX

is it possible to use sheetXA1 together with sheetYA1 as reference in sheet 2 so that i get back test as value.

View Replies!   View Related
VBA To Merge Four Workbooks Into One With Header Row
I have a client that I process their data every Monday and am already using some VBA code on it. I receive their data in four workbooks that have the same name every week.
and the files are put in the same subdirectory every week:

What I'd like to do is run some VBA code that would automatically look in this subdirectory for the four files and merge the rows of account data (rows 2 - the last used row and columns A - S) from these four workbooks into one workbook and have the header row from any of the four workbooks (they're all the same) placed in the new "merged" workbook at row 1.
Could anyone provide to me the VBA code that would do this?

View Replies!   View Related
Master And Slave Workbooks
I have a Master workbook that contains the complete sales for the entire business. This is based on input from 12 different departments. These 12departments have their own slave Excel workbook that they enter information into. The 13 files are all located in the same directory on a network folder.

What I want to do is to automatically gather all the entries from the 12 slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.

Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets. How would I go about doing so?

View Replies!   View Related
VBA Data Transfer Between Workbooks
I have a series of of quesitons but honestly, I think it'll be answered by ONE question.

This is the my request as described below:

Bob, Jim, and Lisa are Project Managers (PM). Each PM has a Project Template Workbook with required info they need to fill out PER job. This Template workbook has a VBA button that submits their info to the PM Master File.

The PM Master file is a seperate workbook that sums up each project as filled out by the Project Template Workbook.

What I need is the code to either copy/paste the values from the template to the master file ensuring that the most recent addition of information goes to the next row and doesn't paste over the existing data.... or ... I need a different approach.

View Replies!   View Related
One Workbook Affects All Workbooks
I have made a workbook file which is a form. I deleted all tool bars except "Print" and "save as". And I have saved this workbook. When I startup XL again to do other work It brings up my same bar arangement as my form. When I change it back it also changes my form file. How do I keep my form arrangement seperate from the standerd excel set up?

View Replies!   View Related
Combine Data From Different Workbooks
Is it possible too do this if they are on the same computer. Lets say I want all the data on page3 in one workbook, data on page2 in another and have it show up in another workbook. If this can be done can it delete cell entry that match?

View Replies!   View Related
Get Data From All Workbooks In Folder
A while back someone helped me out with a code to pull data out of all workbooks in a folder.

View Replies!   View Related
Copyright 2005-08, All rights reserved