Pulling Up Data From Multiple Workbooks
Dec 8, 2009
I have a folder which contains a number of Workbooks which were all built using the same template, therefore the layout and formatting is identical in each one. Cells A1:B15 of these workbooks contain information which I am attempting to extract.
I have been asked to build a worksheet with two cells for user's input. The first cell is the 'Directory location cell' and the second cell is the 'Policy Number Cell.' The policy number is found in Cell A2 of every workbook.
The idea in mind is that the user inputs the directory location in the first cell, the policy number they are searching for in the second cell and then excecutes the macro. The Sheet would then return all of the valuable information from whichever workbook contained it.
I think I need to compose a macro which trawls through the folder specified in the 'Directory Cell,' opened every xls until it found the policy number which matched the value in the 'Policy Number Cell' and then returned the cells A1:B15 into the new Sheet.
View 14 Replies
ADVERTISEMENT
Jul 20, 2009
I have 8 individual workbooks that reside on a networked drive that I need to compile into one worksheet in another workbook. The 8 source files are in a separate folder.
I got the following code from someone on this forum, but I can not seem to get it to work exactly.
Sub CopyFromWorkbooksTake3()
'Declare and define variables
Dim objWB As Workbook, SourcePath As String, wbName As String
Dim LastRow As Long, NextRow As Long, DestSheet As Worksheet, i%
NextRow = 2
SourcePath = "C:/Your/File/Path/"
Set DestSheet = ThisWorkbook.Worksheets("MasterList")
'Identify the directory of interest
On Error Resume Next
ChDir SourcePath
If Err.Number 0 Then
Err.Clear...............
View 9 Replies
View Related
May 6, 2008
Got a requet from a client that we can't do with out accounting software. Basically I have a folder of all the clients indivisual invoices for 2008. We want to put together a report that will include the employee name, invoice date, and amount. But the only way to get this right now that I see is to sift though the hundreds of workbooks we have for them, one by one. Is there something that can be set up to pull 3, 4, or 5 of the same fields say cell A1, B3, C3, and D6 our of every workbook in a single folder on our network?
View 12 Replies
View Related
Mar 15, 2013
I have two excel files that I am trying to gather information from to calculate the average $/hr earned by employees. The first file, which is currently called "Book 1" is generated by a different VBA code, the other "Employee Hour Output" is generated by our time clock program. What I am trying to do is have the script run from the PERSONAL workbook and look in the Employee Hour Output. In column B there are Employee names, for each employee that worked, there is a list of invoice numbers located in column E.
The code needs to see which invoice numbers each employee worked on then compare that to the $/HR earned from that job in the Book1 workbook. Then based on all the jobs worked, calculate the average $/HR that employee earned in the given time period. This information needs to be displayed with the employee name and $/HR in the first available row of columns H and I of the Book1 workbook.
View 1 Replies
View Related
Jun 3, 2013
I am trying to use the following formula to pull out data from multiple excel files in a folder called "Certificate".
='D:SSR Sec and Techcertificate[STUDENT 2.xlsx]Student Record'!$B$10
='D:SSR Sec and TechcertificatePath
[STUDENT 2.xlsx] File Name
Student Record'!$B$10 Sheet name and Cell reference
The formula works without any problem. I want to replicate the formula to extract the same data in multiple excel files. In this case only the second part of the formula needs to change to "STUDENT 3", "STUDENT 4", "STUDENT 5" and so on. I have created a column in excel with those values. I am trying to use the cell contents in the above "formula", but I cannot seem to replace this value in the formula. Needless to mention that I tried to drag the formula, but it does not work. I am attaching the two excel files.
View 3 Replies
View Related
Aug 7, 2013
I have a workbook with 4 sheets one is called final and the other 3 are data1, data2, and data3. The data sheets have the actual data I am needing to pull from. Each has two rows of data: data1 has employee number in column A and employee code in column B. Data2 has employee code in column A and employee name in column B. Data3 has employee name in column A and employee email in column B. Now I need to pull the info form all three sheets into the sheet names final. So the final sheet needs to contain 4 columns for employee number, employee code, employee name, and employee email. The kicker is the data sheets don't line up with each other within each sheet it does but the first one in data1 is not the first in data3 and so on.
View 2 Replies
View Related
May 22, 2014
Basically, I run a DJ Management company, arranging bookings for 6 artists. What I'm trying to do, is have data be auto populated from the Master sheet, into separate sheets. The aim, is to create 1 master with ALL the dates for the month in, and the each of the separate sheets to only populate data relating to that artist.
For example!
Artist
Event
Location
Fee
Date
Tony
Tomorrowland
Boom, Belgium
5,000
01/05
[code]....
Now, I need to send the artists their individual schedules for the month, but I do not want to copy and paste individual rows to their new sheets as this is a pain. I know i can sort by artist and copy it easier, but really i just want to put in the info once and not multiple times.
So the master is sheet 1, on sheets 2,3,4,5,6,7 (each one assigned to a particular artist) i need the artists gigs for that month.
View 1 Replies
View Related
Feb 13, 2009
I am creating a spreadsheet for mutiple clients, which has around 5 columns. This spreadsheet will have 7 sheets on it. 1 sheet for each client, and the 7th will be to display all of the information together.
Basically I am looking to find out if this is possible?
I will try and explain a bit more, as above is just beifly what I am loking for.
I Have Client.xls
On my Tabs I have
Client1, Client2, Client3, Client4, Client5, Client6, All Clients
In Each individual spreadsheet, I have the following Columns
Incident Reference; Description; PMDB number
Each Day data will be entered into each client sheet, and I would like this information to be populated into the All Clients Tab. I am not sure if this is possible, and if it is, would I then be able to put it into the All Clients tab, and have it auto sort by the incdient reference column? As this is an autogenerated reference for all of our clients.
View 9 Replies
View Related
Aug 13, 2012
Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
Attached sample file : example.xls
View 3 Replies
View Related
Feb 26, 2013
I am building a workbook for small group of people in my office to use. I have 7 tabs, the first one is called Main Sheet and the other tabs are the names of each person using the excel workbook. Every tab looks the same, I have the columns labeled: date - job number - job name - contact # - comments
My desire is that each person will be able to input their data on their tab as they receive new jobs, and the main sheet will be a compilation of everyone's sheet. So to clarify, as new information is added to one tab this entire row of information will automatically be added to the next available spot on the main sheet. We are all on the same server so we can all work off the same file. Is this possible to accomplish?
View 2 Replies
View Related
May 15, 2006
I have an identical worksheet for all the days of the month - i.e. the May workbook has 30 identical worksheets. I am wanting to pull only certain rows from each worksheet onto a master. Something like - copy row from all worksheets where that rows cell "D" has any value other than " ".
I have used the following macro to pull all the data from every sheet onto a master - but i don't want all the data - just the qualifying data.
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "ALL"
I am not sure what the range "A1" is - i am thinking i can modify it to only grab certain rows.
View 9 Replies
View Related
Aug 30, 2012
I have a spreadsheet with multiple data tabs feeding a few summary tabs.
For this question I will deal with one summary tab and two data tabs, one data tab for sales and one data tab for service. On the summary tab, I want to look at vehicles sold during a certain time period-(looking to the sales tab). For each of the records in the sales tab that match the date range, I need to count the repair order activity on the service tab. The key between the summary and sales tab is the sales date, and the key between the sales and service tabs is the vehicle serial number.
I currently have multiple columns on the sales tab to perform the counts from the service tab, thus allowing me to pull the information to the summary tab. Problem is that the spreadsheet has over 2 million calculating cells and tends to take excessive amounts of time to recalc.
View 2 Replies
View Related
Jun 16, 2014
I've already entered a variation of the formula below into 180 different columns. The only variation is where worksheet 322 is referenced. Each column references a different worksheet.
Formula:
I have at least four other tables to build of the same size, and they're each going to use this same formula with an additional IF formula housed around it. Ideally I would be able to copy the table, then run find/replace, where I could substitute = with =IF(new formula, and then run find/replace a second time and sub ))) with ))),more new formula). The problem of course is in between those steps lies a formula error preventing me from running the second step. The only alternative I can think of is to build the new formula, copy it into a word document, and run find/replace 180 times to tailor the formula for each column. That's what I did to build the first table.
View 8 Replies
View Related
Mar 14, 2012
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
View 3 Replies
View Related
Jul 12, 2014
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
View 4 Replies
View Related
May 14, 2014
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
View 2 Replies
View Related
Aug 2, 2013
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
View 1 Replies
View Related
Oct 29, 2009
I am trying to create functions in VBA and I need to extract data from multiple workbooks. For example, is it possible to sum data(numbers) contained in multiple workbooks using VBA? If so, how do I create that function in VBA.
View 9 Replies
View Related
Jul 18, 2006
I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?
View 5 Replies
View Related
Jun 14, 2007
I have 2 Excel templates using lookups to create customer quotations. When complete a macro is run which strips out formulas and links to data.
I would like to record certain data from the finished workbook in another workbook on the server e.g. quote number, date, customer, total value, follow up date.
Is it possible to include another macro that records data in certain named cells to a separate workbook when the file is saved?
View 9 Replies
View Related
Jun 14, 2007
I've got a variable row length list of data, 24 columns wide with column headers in row 7 and file descriptions on the above rows, that I need to split up into seperate Workbooks dependant upon values in the last column, all rows with the same value in column X form the same new Workbook, which I can then SaveAs with a name associated with the value. I've seen a similar thing done to sheets on the forums by using the Advance Filter and I would adapt that to copy each created worksheet to a new workbook, but a) this seems inefficient and b) my unique values are too long to name sheets from.
View 6 Replies
View Related
Jan 10, 2008
Engineering has given me 300 separate 2003 Excel Files. Each of these individual Excel files contains data I need for creating a list I have been tasked to do. Cells E3, K1, A6 and J2 in each Excel file contain the data I need for my list. Rather than manually data mining 300 separate Excel files for data I need from Cells E3, K1, A6 and J2, can this be done automatically with a macro?
I'm seeking for the Macro to automatically OPEN each individual Excel file, then copy the data from Cells E3, K1, A6, J2, and then paste this data into a list file with four columns. After the pasting is done, the macro will close the source data Excel file and move on to the next one. In the future I will have to repeat this task with probably 400 individual Excel files. Therefore, it will be great if the Macro didn't care how many Excel files required data mining.
View 4 Replies
View Related
Oct 17, 2013
I'm trying to solve i have multiple books but each book contains same information e.g
Book1
Sheet 1
Date
Name
Sheet 2
Location
Postcode
Book 2
Sheet 1
Date
Name
Sheet 2
Location
Postcode
etc ... there is also multiple sheets in each book all are same layout just each book is different customer.
I'm trying to do is have a Listbox that shows all customer name and link back to His/her file location. Then retrieve certain information and display on blank workbook?
Question:
Can this be done without opening workbook
Considering i have 20+ workbooks and growing is this even possible
Cannot merge workbooks into one as this info is supplied by third party
View 1 Replies
View Related
Aug 13, 2014
I'm trying to set up an excel that will allow me to gather data from multiple workbooks and get it into one master worksheet. All of the worksheets are formatted the same way (See below)
Employee Last Name
Employee First Name
Employee Position
Employee Series Number
Departing City
Departing State
Attended Pilot Training?
We don't have the data yet so the idea is to paste this information into their perspective worksheets and then have them automatically populate into the master tab.
View 1 Replies
View Related
Jun 16, 2014
I am trying to create a macro to gather a data range from multiple files placed a folder and combine them into a single worksheet which can be easily totaled. I've used some similar code I did for another project to gather the data. It starts by listing the file name and then the data set (About 40 cells) below the file title. However, the data sets from each spreadsheet are filling themselves into a single column one after the other, whereas I would like to have them populate one worksheet's data in each column.
[Code]......
SummaryWorkbook.xls
View 1 Replies
View Related
Jun 26, 2014
I have two workbooks with similar columns (note: both workbooks may not have all the same columns, but for the same columns, the names are written identically in both workbooks). I'll walk through what's in each sample workbook I've provided and what I am hoping to accomplish.
Transfer Rows1: This workbook contains multiple rows of data for one company or ID (For example, Client "A Co" has 4 rows of data.)
Transfer Rows2: Contains some of the same columns as workbook 1, but not exactly in the same order.
What I want to do is take all the multiple row data for an ID in Transfer Rows1 and put the information as a single row in Transfer Rows2. So, Client "A Co" in Workbook 1 has 4 rows, but in Workbook 2 will have 1 row. It's matching columns, essentially. Columns that are present in both workbooks should have the data filled out in only ONE row in Transfer Rows2.
As far as cost is concerned, Cost A in Transfer Rows2 is just the sum of all the Cost A's for a particular ID Number in Transfer Rows1 (same for Cost B and Cost C).
I also want the installation date in Transfer Rows2 to be the latest installation date for each unique ID from Transfer Rows1.
Also, a particular client can have multiple CID's. This appears in different rows in Transfer Rows1, but should appear in one row, multiple columns (again, for each unique ID) in Transfer Rows2.
Notice, the column 'Products' in Workbook 1 is not in Workbook 2, and that's because each row for column may have different information and thus I would not be able to transfer it into a single row in workbook 2.
TransferRows1 is where I am right now and I have filled out TransferRows2 to indicate what I hope to accomplish. I am posting here because the actual set I am working is fairly large, on the order of ~ 35,000 rows and need to automate this process using code instead having to copy and paste thousands of times.
View 2 Replies
View Related
Nov 5, 2008
I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.
Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.
From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:
Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.
View 14 Replies
View Related
Sep 5, 2007
In my VB6 project I need to loop through several Excel Workbooks with the same chart format and amend the chart data as required from time to time. There are 2 charts in Sheet2 of every workbook. With the following coding, I can only update the chart in the first workbook but not the one in the second workbook and the rest. It appears that there will be no further updates after the first workbook is closed.
Option Explicit
Dim oEquityWB As Excel.Workbook
Dim oEquityWS As Excel.Worksheet
Public Sub AmendChart()
Dim arrWorkbooks() as String'Path & Filename
Dim nn as Integer
For nn = 0 To UBound(arrWorkbooks)
If Not EquityWBOpen(arrWorkbooks(nn)) Then GoTo Next_nn
Set oEquityWS = oEquityWB.Worksheets("Sheet2")
oEquityWS.ChartObjects("Chart 1").Activate................
View 9 Replies
View Related
Nov 10, 2009
I do environmental testing for multiple hospitals and surgery centers. I've created a master workbook in excel 2007 that includes about 7 sheets. Each sheet is for account info, testing areas, billing, and reports. In the testing areas sheet, I've used a formula to compute in column F a return date for each row based on when the area in that row was tested AND based on that area's yearly schedule (quarterly, semi-annually or annually). The date tested info is in column E of each row and the yearly schedule is in column H of each row.
Every client has their own workbook created from the master workbook template. I would like to export the return dates data in column F of each workbook into a single workbook that would tell me what testing I have to do for each client in any given month so I don't have to go to each workbook and make a list manually. So, not only the date would have to show, but also all the other info in that row (acct number, location, charge, etc). BTW, the data in this testing areas sheet I want info from is not in a "table"...just in cells.
View 9 Replies
View Related
Sep 23, 2007
I have been working on this project for some time and still haven't got anywhere with it. What I am essentially trying to do is create a master summary workbook where by you press a button to activate a chunk of VBA that will grab the same range of data from multiple workbooks but also multiple sheets in those workbooks, only problem is the number of sheets in each workbook are constantly changing so I need first find some way of accessing a closed workbooks, then I need a way of looping through a segment of code that will look into approximately 10 workbooks all with a different number of sheets and taking a fixed range of cells out of each sheet and for every workbook make a new tab and paste the information in that tab then move onto the next workbook, find the number of tabs (missing out the first 3 tabs of every workbook) and copy and paste the fixed range from each tab onto a new tab the summary workbook.
I will then have a summary workbook with a number of tabs, each one a different workbook with all the data from all the tabs in that workbook, I can then pull that information using formulas or some more VBA into a summary tab on the summary workbook, consolidating all of the information.
View 6 Replies
View Related
