I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?
Is there a way to lock and unlock multiple excel spread sheets with only one password at one go? For example if I have 5 sheets, I would like to be able to lock and unlock Sheets 2-5 with one password at one go while leaving sheet 1 unprotected. Do not want to type a pwd for each sheet.
We have 13 salesman each completes a quotation log (each log is identical - except for the data obviously)
For month end and reporting purposes it would be useful to have 1 single log sorted by date.
It is not possible for each sales man to input the data into one log as some are away on business and take the log with them.
The logs are all stored in the same directory however there are other files and excel spreadsheets in the same directory.
This is how I would like to see it working - On opening the "MASTER" sheet it automatically (no user input required) clears out the old data, then gathers all the data from 13 spreadsheets, sorts it by quote date and dumps it into a new workbook.
We will be using excel 2000, 2003 & 2007 I can enable macros on all machines if necissary.
The spread sheets are very simple, single sheet with no formulas - just manually adding data. There will be no blank rows and data will be held in the range A - S with rows 1-4 being headers and titles
I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010
I have two very large spread sheets that are worked on individually. Because of there size, sharing them causes issues and lock-ups (excel not reasoning) and lengthy download time.
They are now worked on individually with two separate file names.
I am trying to find a way, even a program, to at the end of each period, combine the files and update the data that would have been input.
I have attached examples of what might take place.
A B C D E F
A B C D E F
As can be seen, Example 1 has had information added to Columns A, B and C, whereas Example two has had info added to Columns D,E and F. Also, the data in Example 1 (E,3 and F,3) has been updated to reflect No with F,3 changing to "Blank"
The result is to combine both spread sheets and update the added / amended data into one spread sheet that shows an updated position as follows.
I have three workbooks. Every workbook has 50 persons IT information. Those are CPU list, Monitor list, UPS list. I would like to create another workbook. Where all user's IT information will be available. Like X user's information required, under x information his CPU, monitor and UPS information link will be available. How to create link of the CPU, UPS monitor sheets with new workbook?
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 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'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?
I have a workbook with two spreadsheets. One contains customer information. The other line items and shipping information. I would like to pull the customer number over to the line items spreadsheet. Both spreadsheets have the order number, but note there is a new line for every item in an order on the line items sheet. How do I accomplish this?
The company deals with Machine services, we maintain, service and brake-fix machine problems, the machines are manufactured my ourselves and there are approximately 15 different machines.
We have a Field Service spreadsheet that is filled in by all service engineers for every job they go on, this is filled in with information like:-
Serial Number Machine Type Hours Run Time Reason for Visit Reported Problem etc, etc
All the forms are identical so all the fields are identical
Field Service Record Spares 2013 Master Rev4 (2).xlsx (this is the correct file). The forms are then saved on a central server folder and stored in month order.
Is there a way of creating a master excel sheet to gather all the information from all the Field Service spreadsheets which would then allow me to search all sheets by serial number or fault, without me having to copy and paste all the information into the master sheet??
We have 10 service engineers and each fills out 1 sheet per job per week, so a 4 week job will have 4 sheets for 1 engineer. an engineer could go on 5 jobs in 1 week and would therefore fill out 5 sheets. so we could be looking at in excess of 500 Field Service spreadsheet per year.
I currently have a macro that can be used by multiple spreadsheets. Is there that I can execute the macro within any specific spreadsheet without saving it in each spreadsheet. I just want to place it in a general spot so that in case there is modifications, I only have to change it in one place.
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.
'----------------------------------INFORMATION---------------------------------------- 'You need to make sure you have activated the Scripting Runtime reference for the FSO to work '-------------------------------------------------------------------------------------- Dim fso As New Scripting.FileSystemObject
I have 5 sheets in my workbook and I need to find out if a string exists in any of them. The string would always be in column L of the work sheet. I have searched many places but cant seem to get anything to work, is this possible?
I am attempting to amalgamate several (100+) spreadsheets into a single spreadsheet. The data is very simple (only three columns, several rows). I have written a simple macro to copy 5 spreadsheets (called TIMESHEET1, TIMESHEET2 etc) into 1 spreadsheet called AMALGAMATED TIMESHEETS. I would like to expand this using a FOR - NEXT loop if possible - or any other way that someone can think of! I cannot use the variable TIMESHEETx as this is not accepted......
Is there a way to automate the importing of macros into a list of files ? I have a number of files that I want to add some code to and was wondering if this could be automated as opposed to adding the code to each file manually as it's going to be a regular job.
I've written a routine to loop through the list of files and open them, I just want to know how, if possible, i can automatically import the required macros.
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.