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.
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
Macro to collate four workbooks into single workbook.
Sheets in workbooks also to be copied. The sheet 1 of the 4 workbooks should be combined into the sheet 1 of the new workbook. Sheet 2 of the 4 workbooks should be combined into the sheet 2 of the new workbook, and so on.
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.
I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.
I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))
I could use some help in modifying it so that instead of creating new workbooks to copy to, it copies to EXISTING workbooks whose names match the YEAR ("yyyy") in the Dates fields in column "B" (and only creates new workbooks for non-existing Years, IF needed).
Each workbook name is a "Year" (ie. 2000, 2001, 2002, etc.), and all unique data in A:D are to be copied to each corresponding Year workbook, again as per the Year in the Date field.
I’d like both columns "A" AND "B" tested to make sure that only original (non-duplicate) data is added to the corresponding Year Workbooks (all residing in one folder).
Btw, the code also copies the header, which is ok for new workbooks, but not needed for already-existing wbs.
End Sub SpreadsheetA.xls has a macro called "ExpectedRun" which is currently operated by clicking a commandbutton.
What is the code to run the "ExpectedRun" Macro in Sub OpenA?
Eventually I'll be opening a bunch of these workbooks, each with its own unique "ExpectedRun" macro, so is there any special command to call on the "ExpectedRun" macro for specifically the opened workbook?
I need to extract only unique email addresses from all the workbooks saved in C:EDT and its sub-folders. The sub-folders will be yearmonth. So there would be several workbooks in each C:EDT2010JAN, C:EDT2010FEB, C:EDT2010MAR...etc, etc. Also, in the future they would be in C:EDT2011JAN, C:EDT2011FEB, C:EDT2011MAR...Same for 2012, 2013, etc, etc.
In each workbook, the email addresses will be located in Column K of a worksheet called ACCOUNT. There is some other info in the column (Phone, Title, Name), but I don't need that.
I would like the email addresses copied and then pasted into either a new MS Word doc or a new Excel workbook/sheet. All the workbooks that contained the email addresses should then be closed.
I have a workbook with about 10000 rows of data for about 100 suppliers in Sheet1 and about 15000 rows of same Suppliers payment details in sheet2.
What I am doing now is:-Filter every supplier names in Column A of sheet1, copy all the rows and paste in sheet1 of a new workook & Again copy the Payment details of a same supplier from master file sheet2 and paste the same in sheet2 of this new workbook then save the files with the supplier name as file name in my documents folder code, that filters each suppliers data from sheet1 & 2 and paste in new workbook sheet1 & 2.
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.
I always copy data from all tabs to a single tab. I am looking for a macro which does this and saves time. Criteria is Data starts from Col B to Q not further in all tabs, but the rows will vary in all tabs.
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 column...so 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.
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?
I have a data on sheet1 which goes from A2:T unlimited, I already have a macro that enables me to pull data from A2:T2 that then collates to sheet2, but I need it to then go to the next populated row which will be A3 and collate the data then progress onwards until final row.
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.
Every month we collect data from a number of employees and average it out in a new excel workbook ready for analysis. Each employee sends in their monthly data, structured identically, with only the numbers in the data tables different.At the moment, we just copy and paste every single data return into the separate worksheets of a single workbook, and on the front page of the workbook we create a simple formula which adds the equivalent cell in each sheet together to get a total. Its not hard to do, but its very lengthy as a process.What I've been researching, is the possibility of a macro that can automatically send the outputs of separate workbooks into one central workbook, so that we don't need to go through the copy and pasting of each return every month.
E.g, Say I have 10 workbooks, named Book1, Book2, Book3 etc, which each have a number ranging from 1 - 10 in cell A2. Could A macro be designed to bring all those values together into cell A2 of an 11th workbook, e.g one entitled book11? I'm aware that said Macro may need to exist in each separate workbook in order for the process to be carried out.
I'm trying to collate data from two sheets (sheet1 & 3) , paste it on sheet5 and do some calc. But the macro keeps copying the same row after 2 iterations. Also the find function stops working after 1st iteration. Not sure what to do
Sub Macro17() ' ' Macro17 Macro ' ' Sheets("Sheet1").Select Range("B3:L3").Select Do Until Selection.Row = 10000 Selection.Copy
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".
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?
I've been trying to copy values from one range to another, and the ranges need to be dynamic. Normally I use a combination of the 'range' and 'cells' properties, like this:
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE] ClassAB 1032 1511 2012 [/TABLE] Can this be achieved through a formula?
I am creating a macro to copy data from one workbook that is emailed to me to another workbook that contains the actual macro, I will be using the second workbook to import the data into an application.
What I need is to be able to copy text in cells say D1, D3, D6 etc located in workbook1 to cells say A1, A2, A3 etc in workbook2 without having the macro go back and forth each time to copy the cells one at a time.
Also is there a macro that will recognize an already open workbook that I can use to copy the data
I have 194 Workbooks that all contain data on the first sheet only. I need to go into each sheet and pull the information from cell B6 and F21. That information needs to be copied to a new workbook into columns A and B so when I am done I have 194 lines in two columns. Each of the workbooks I am pulling from have the info I need in Sheet1.
Is this possible, or do I need to do a lot of copying and pasting? If it is possible, how do I do it?
I have a column (within a database) containing hundreds of Project Numbers (e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear more than once in the column. I need to count the number of unique Project Numbers within that column, based on the criteria of another column. Is there a way to do that using a combination of the DCOUNTA function and a formula?
I have multiple workbook files using the same template but saved under different files names usually by date. I need to summarize the data in a summary worksheet that pulls the same cells from the various individual closed workbooks. I need to be able to insert the file's path as an input to pulling data from a cell or cells.
I have a woorkbook with a sheet for each week. What I want to be able to do is select 2 cells and click a macro button that will ask how many weeks to copy these cells to.
I could then enter, say "10" and the cells would then be copied to the ten proceeding weeks workbooks. Assuming that the sheets are in order!
I am having a bit of trouble with something i am trying to do in my Excel workbook (which we will call the home workbook). Basically what the home workbook does is opens a number of different Excel workbooks that are located in a certain directory. It then finds the first blank cell in on the worksheet "Data Import" ...the cell adress is stored in a variable called found. I need to utilize the values that are in certain cells of the workbooks that the home workbook is opening, but I keep getting a 'subscript out of range' error. I am almost positive it has something to do with accessing the other workbooks. Here is the code I've come up with (well parts of it that are relevant):
Dim found As Range Dim strFile As String Dim strPath As String
strPath = "C:directoryPath" 'amend directory as appropriate strFile = Dir(strPath & "*.xls") ' amend extension as appropriate Do While strFile <> ""
