Pulling Same Cell Values From Multiple Workbooks
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
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
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
View Related
Apr 22, 2014
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
View 5 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
Oct 7, 2009
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
View 9 Replies
View Related
Feb 20, 2014
I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:
[URL]
As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:
Code:
'Import from www.basketball-reference.com
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.basketball-reference.com/friv/dailyleaders.cgi?month=02&day=19&year=2014" _
, Destination:=Range("$A$1"))
.Name = "dailyleaders.cgi?month=02&day=19&year=2014"
View 2 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
Apr 27, 2007
I got a problem that I cannot seem to solve, I want to retrieve values from 2 closed workbooks, I have with the help of this forum managed to create some VBA that retrieves the values from one of the workbook. What do I need to think of,to be able to put in the values from the second spreadsheet without the values disapearing from the "master" spreadsheet?
The code so far:
Sub ValuesfromClosedWorkbook()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
Set wb = Workbooks.Open(filetoopen, True, True)
With ThisWorkbook.Worksheets(1)
. Cells.Value = wb.Worksheets(1).Cells.Value
End With
wb.Close False
Set wb = Nothing
End Sub
View 3 Replies
View Related
Feb 13, 2009
I have several workbooks in a folder with sub folders. How can I get the values located in C6,
E6 and E9 and put in a new workbook the "file name" in Ai and those 3 values in Bi, Ci and Di
respectively? Itīd be desirable get the values without open the files.
something like:
------A-----------B---------C----------D
Filename1.xls---13:11------13:57-----00:46
Filename2.xls---17:42------18:03-----00:21
.
.
View 9 Replies
View Related
Feb 29, 2008
I need to link to a different workbook but using cell values.
For example, in the attached file in cell B4 it looks like this:
='C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls]Week 9'!$D$20
I need a way to change Week 9 in the above formula using a validation list.
I would also like to know how to specify the above workbook referenced using a cell as well, for instance, C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls] would be the contents of a cell or the ability to click on a cell and have it bring up an open dialog box to browse to the file and input it's path into that cell.
View 4 Replies
View Related
Jan 12, 2007
I have 400 workbooks to examine if cell P54 is 88% or lower. Is there an easy way, or is VBA with a loop the way.
View 5 Replies
View Related
Mar 23, 2012
I have a little over 100 workbooks which I will receive back from customers and in column A of another workbook I have the names of those workbooks.
Not all workbooks will come in at the same time, but I would like, as we receive the workbooks, retrieve the value from cell H19 from the available workbooks according to the name in column A and place the value in column B.
I've looked into Indirect, but with this function the workbooks have to be open. If one of the workbooks has not been received, I would like for the macro to skip this file name. All files are .xlsm.
The file will be in the same folder as all the individual workbooks.
Column AColumn BFile NameH19 ValueDallas.xlsmSan Diego.xlsmArgentina.xlsmParis.xlsm
View 3 Replies
View Related
Nov 12, 2007
HOW TO pull the unique values out of a column of entries and put them into another column.
For example I'll have a list of maybe 2,000 but with only 1-7 different values in the column, what I want to do is populate another column with only the list of the 1-7 values that appear in the larger set.
View 14 Replies
View Related
May 10, 2013
I'm looking to create an automated table with formulas to populate based on multiple criteria in a data sheet. I want to pull the person with the highest SALES but meets every criteria listed.
Currently I have it set up like:
Name | Sales | Penetration | Score | Target, etc.
But each criteria has a minimum/maximum criteria that they have.
I'm wondering, how would I be able to pull the NAME of the individual with the highest sales but meets criteria such as Penetration, Score and Target.
View 9 Replies
View Related
Apr 20, 2012
I would like to get data onto my worksheet that looks something like what I have below. I'm pretty set on using Dictionaries to get the data into this format
(row 1 reads): header 1, header 2, header 3
(row 2 reads): value 1, value 2, value 3
(row 3 reads): value 1, value 2, {"property1": property1, "property2": property2, "property3": property3}
I'm close to doing so but having issues with my syntax.
I can refer to:
arrExport(1)("key3") to get the value in key3
but I cannot refer to:
arrExport(1)("key4")
Because key4 is what contains another dictionary.
Code:
Sub DictWithinDict()
Dim dict As dictionary, dictValue As dictionary
Dim arrExport() As dictionary
Dim i As Integer
ReDim arrExport(1 To 10) 'real data will be in thousands
[Code] .....
View 3 Replies
View Related
Jun 12, 2014
I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.
I've attached an example spreadsheet for reference. Basically, I need two formulas:
1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).
My actual data file has 80+ senders and 100+ receivers.
View 6 Replies
View Related
Oct 3, 2012
I have been trying for months to get this to work by altering code from similar requests I've hunted down on google to no avail.
I am looking to have a "Master" sheet that is populated by data from 30+ individual excel workbooks. Each workbook is contained on a server in a directory "O:JobsJobs In Progress." In that directory are subfolders (named in sequence "C12000 Job1", "C12001 Job2", etc) which contain the actual workbook (Named according to number ie. "C12000 Jobinfo").
I would like this "Master" to automatically pull specific values from each C1200* workbook and paste them in a designated cell so that I can quickly look at specific information contained in each workbook. Each C1200* workbook is constructed the same way.
Short Example of C1200* workbook in which data will be pulled from:
Job Number
C12000
PM
Abe Lincoln
Foreman
Thomas Edison
Contract Date
1/1/10
View 1 Replies
View Related
Aug 21, 2009
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
View 5 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
May 4, 2009
I need to be able to pull the information into another section of the workbook and place it into order (ie Black 1,2,3,4) and then drop down to the next row and place Blue 1,2,3,4 etc. I am using a drop down menu to select the Course, and want the times to then populate to the right, each in its own cell.
Have tried the loop code available on a few different topics here however has not quite worked.
View 12 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 8, 2013
I have a work book with prob close to a thousand individual work sheets in it. I have a goup of cells that are the same in ever work sheet that I would like to pull and place into a master work sheet. I dont want to copy and paste each one, I'd be a hundred before I finished and I dont have that kind of time. Im thinking a macro might be what I need but Im not very good with useing them let alone making one to fit me needs.
View 2 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
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 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
