I have created with most of it being done by Austrada (who has done a great job) code to copy multiple spreadsheets into 1 spreadsheet. We have run into a error Run Time Error '9'. Subscript out of range.
'You need to make sure you have activated the Scripting Runtime reference for the FSO to work
Dim fso As New Scripting.FileSystemObject
I am trying to find a code that will allow me to generate multiple sheets depending on what is selected in one specific column. I have found a code that is perfect for this however I can't seem to set the range. For example instead of copying all of column headings I only need to copy up to column AN2.
I have a workbook to collate ratings for staff, there are three sheets, one is a summary of data, and two are sheets that can have data entered into them, called "Eligible" and "Ineligible".
I would like to write a macro to search cells in "Eligible" that contain a staff number, and copy this data to "Summary". If excel encounters a blank cell, i.e it has no staff number in it, it will move to she sheet "Ineligible" and perform the same operation, pasting the data below that which has been copied from "Eligible"
To further complicate things, every time the macro runs to populate "Summary" I would like it to clear the existing data in "Summary", to allow for deletions in "Eligible" or "Ineligible".
I want to create a single spreadsheet containing a selected row from each of 365 spreadsheets;
Background: (1) I have 365 Excel spreadsheets (1 for every day of the year); (2) Each spreadsheet contains: (a) 24 values (one for every hour) horizontally with hourly average temperatures; and (b) 8,000 records (vertically) for 8,000 different locations;
I want to create a new single spreadsheet in which I select a specific location (say, Linden, New Jersey), and capture the temperatures for all 24 hours (horizontally) and all 365 days (vertically);
the file names are all;
20070101.xls, 20070102.xls, 20070103.xls, ............ 20071231.xls (one for each day of the year)
(I tried using =VLOOKUP(Linden,CONCATENATE(A1,A2,A3,"'.xls'!","$B$1:$CA$8017"),3,FALSE); where A1, A2 & A3 are year, month & day respectively) but was unsuccessful;
I'm on a BTEC in college and my tutor wants me to create 3 spreadsheets, one of them is a Master spreadsheet which shows each pupils grade for each module, their over all end year grade and how many UCAS points that grade will earn them. The other two sheets are for the modules which will have the grades of the pupils in that lesson, Pass, Merit, Destinction or Fail. I hope you're still with me... I've attached an example of one of the 'Slave' spreadsheets as I call them.
My problem is that I can't seem to find a way of doing all this without constant nesting of COUNTIF. The code I have for my 'Unit1' spreadsheet's grade is:
It works, but it's very messy. If this sounds confusing I apologise, the attached documents should clear some of the confusion up. Once the smaller spreadsheets work, the Master needs to use them to update it's cells. If someone has a Merit grade in 'Unit 1', the Master needs to show it too by somehow linking the 'Grade' cell from 'Unit1' to the 'Grade' cell in the Master spreadsheet.
I need to have totals from individual worksheets automatically enter into cells in a master spreadsheet.
What I am doing is keeping track of donations collected from individual departments - each on their own worksheet. I would like to have the totals of each page automatically enter & update onto a master worksheet that would show the totals from each dept and then give me a total of all those.
I am not that well versed in Excel. I have been able to set up the individual worksheets and the master.....but can not figure out how to accomplish what I want with the Master tally sheet.
On the individual worksheets I also want to set it up to give me the average donation per person. So if I total the # of donors and the total $$$ amount...what formula do I use to get the average?
i am trying to do collect data from 4 different spread sheets (they all consists of the same columns but they do not have the same amount of rows) and rank them based upon one of the columns in a fifth spreadsheet. in the fifth sheet i also want to display all of the information found in the four sheets, see simplified example below. Is this possible? and how do i do it? when i googles it i only found ways to do it using macro but i know nothing about macros..
Sheet 1 A 3 W B 5 X
Sheet 2 C 2 Y D 7 Z
What i want excel to do C 7 Z 2 B 5 X 1 A 3 W 1 C 2 Y 2
I am trying to build a workbook to track patient treatments. My overview sheet needs to list the last treatment received per patient which is listed in column a of each patient's individual sheet dynamically so I can review the history of treatments as well. I have tried to create a dynamic list, but it is not functioning correctly. I was using : =OFFSET(txdate,1,0,COUNTA('patient, name'!$A:$A),1) , where column A ( the named range "txdate") lists the treatment dates in succession and should be updated automatically when a new treatment is done.
I have a main spreadsheet that I am consistenty adding information to. The columns are : Company name, Contact, Territory, and Status. The main spreadsheet is titled "Main". I want 5 additional spreadsheets in the same workbook that are automatically pulling information from the "Main" spreadsheet, and populating the appropriate spreadsheet . For example...I have 500 entries of different companies in "Main". All of these companies are either categorized as "North, South, East, West, Offshore" in the Territory column. So, I would like my workbook to have 6 tabs...one "Main, North, South, East, West, Offshore." As of right now, I am Sorting the column, then copy and pasting into correct spreadsheet manually.
I have a sheet from which I load data. A normal cell looks like this:
I wrote a script however that writes new data into a new sheet every day, and saves it with the name of the date. The obstacle is, that I won't have a sheet for each day (ex: weekends, holidays). For that reason, is it possible for excel to scan a certain folder, and open the 20th file when sorted by date to read from?
I need to create multiple copy of workbook from a named range called - CoCode - range is on sheet called - Data - cell range - L2:L37. Problem is that i want each workbook named as the name that is in cell range (CoCode). Also in same workbook in a sheet called - Company Summary , in cell J1 there is validation list that refers to range same (Codode) i want that to change in each workbook.
I.e 1st copy of workbook name is equal to 1st reference in range CoCode and same 1st reference in Validation list in - Company Summary - Sheet in cell J1. Also selecting Validation populates several sheets in workbook to information revelant to that company code selection in cell J1, - there are no external links to this document-
I have tried to write the below VBA to copy a specific cell to a specific workbook. I have set the folder path in B1 and listed the file names in column E4 onwards. E1 being the number of files in column E. I get a run-time error 91 "Object variable or With block variable not set" on Current File = ActiveWorkbook.name.
Sub UpdateParameters() Dim CurrentFile As Workbook Dim wbOpen As Workbook
I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.
I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.
The information I need to transfer from Issues is: .....
I have a workbook containing 101 sheets on the first sheet is a list of numbers in H1 to H100. Sheet2 will have the value in Sheet1 H1 in cell G3 then Sheet2 will have the value in Sheet1 H2 in cell G3 and so on for the 100 sheets Sheet101 will have the value in Sheet1 in cell G101 in cell G3 how do I automate this task. This would be easy on a small number of sheets to enter manualy but I dont fancy entering into 100.
I have a spread sheet where I need to update columns "H" and "I", the information comes from the host system as an excel download.
Both spreadsheets have the entity id in column "A". Both spreadsheets are in the same workbook The tabs are:
CAM Exp OPEX
What would be nice is a macro that would compare column "A" in both spreadsheets and where a match is found copy the information on the OPEX tab in column "H" and column "I" to the CAM Exp tab in the same columns. (Column "H" and "I")
I get several workbooks from co-workers in other cities. They include data like the total run time for production. When I copy the time 24:03 fom one cell in their spreadsheet to my spreadsheet it shows up as 0:03. I've already double checked that I'm grabing the right cell and that the format for the cell is correct.
I am trying to compare values in column "A" of my PRIMARY spreadsheet with values in column "A" of my SECONDARY spreadsheet. If match in PRIMARY is found in SECONDARY, copy column b and c from SENCONDARY and paste in b and c of PRIMARY. Next, test for next true statement until no other matches in PRIMARY.
Any ideas of how to quickly and efficiently accomplish this task? Actually, I am now copying a spreadsheet from another workbook and pasting the content to another sheet which I am referring to as SECONDARY in the previous paragraph.
I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.
Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.
I have a workbook that I add about 20-35 tabs in a month. To individually go through and protect the sheet will take too much time, is there another way where I can select multiple tabs so the worksheets can be protected?
i have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file.
is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file.
or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.
I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.
There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.
I am creating a MIS for my team. But I am now struck while creating dashboard. I have a workbook with 12 raw data spreadsheets for specific months. Now what I want is that in 13th sheet if I select January then 1st cell in 13 sheet will show the value January cell 1 value. However if I select February then 1st cell in 13th sheet will show the value of February cell 1 value.
I have several excel spreadsheets by which I would like to add together certain values in the column with the same columns of all the other excel spreadsheets. So basically, If one of the values are "SELF" , I would like to calculate how many times that value occurs in all the spreadsheets. Any easier way to organize the data to be able to do this also.
I have a master spreadsheet that links to various other spreadsheet for budget figures. I have now created and updated the budgets for the 2008/09 budget year.
It is painstaking to update the links and browse to the new files one at a time and point them to their new folder. Is there a way I can update all of the link workbooks to the new directory in one fell swoop?