How to Create a Work Breakdown Structure (WBS) in Excel automatically with given data through macros.
In the attached excel sheet
work_breakdown_structure_.xls
Is there any way to do it for data automatically?
I was sent this file and I want to replicate the bill of material tree structure on the left. I'm not sure how it is done.
Image: BOM_example.png
I am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
I would like to automatically create a filestructure in c:projects based on entries in sheet1.
Column A
Row 1: Admin
Column B
Row 1: Meetings
Row 2: Div
Row 3: Presentations
Column C
Row 3: Master presentations
Row 4: External presentations
I have a fixed structure used for all projects, but it differs what each project is using of the structure.
Question:
1. Is it possible to loop through this sheet and create the structure in a predefined location
2. Optional I would like to use a column after/before each folder in sheet 1 where I can use ' ' for not making the folder and 'x' to create the folder.
I am trying to create a folder structure before dumping a CSV export frm the Excel. I am trying to use MkDir but it gives me a run time error 76.
I am testig the code in my PC. Pleaselet em know if I have to change any configuration in excel file.
Also, is there a way to call the windows system command and create the folder structure?
******** type=text/javascript>*********>******** type=text/javascript src="http://pagead2.googlesyndication.com/pagead/show_ads.js">*********>
I tried locking an excel document and i ticked a box that said protect structure, and then entered my password and now I cannot open the document. It is a white square on my desktop now with no options to do anything, it cannot even be attached to an email or deleted. This was on excel 2008 on a macbook.
I have table in excel sheet and i want Create an Excel User Form display automatically to this table, how?
View 3 Replies View Relatedfind the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View RelatedIs there any way that Tab should automatically jump to next column, after the entry has been made.
I have only one number (one digit) per column. I wish that when I enter the digit the tab should go automatically to next column. And also if it is possible that tab may miss the blank column and go to the next where the entry is to be made.
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER()
Select Case Range("F4").Value
Case "Medewerker"
Range("S2") = "M"
Case "Interview"
Range("S2") = "I"
Case "Data"
Range("S2") = "D"
Case "Observatie"
Range("S2") = "O"
End Select
End Sub
Each row of the spreadsheet from a6:a25 is the planned production of one cellbuilder.
From this sheet I need to produce a work schedule of tasks to be performed by date.
Detailing, batch, cellbuilder number,weather 1st or 2nd graft etc, and the date available (for picking)
I would like the option to choose a daily or weekly schedule.
Is this possible ? and how would I even start to go about it. I just can't visualise how to do it.
Create a workpattern using something similiar to the Networkdays function. I want to be able to calculate how many working days are between 2 dates if the employee works a non standard Monday to Friday Week. For Example if someone works ever Mon, Tues, Wed how many working days are there for them in June.
I'm trying to open a file and perform various tasks. This is the structure:
Dim xlApp As Object
Dim xlWb As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls
xlWb.Worksheets(1).Activate
Debug.Print xlWb.Worksheets.Count
Dim ws As Worksheet
For Each ws In xlWb.Worksheets
Debug.Print ws.Name
ws.Activate
Next ws
I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.
Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.
I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.
The working code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False
End If
End Sub
N5 is the linked cell for the option button.
Ive got a problem with a work book im working on at the moment, my company has various different documents created in excel, like a price list, cost price list & stock levels (all Independant) I have had the idea to join them all into a single shared workbook using an idea I found and modified over a year ago on this site, that had the promise of allowing multiple users to see the nessesary parts of the same document. the advantage of this being any new parts or prices or suppliers added would update all users at once. So ive got this workbook that when you open you get asked for a username & password this then hides/unhides, protects or unprotects to suit the users needs. This works great when unshared but when I share it the login box wont display
The Administrator username is jamie and the password is joshua
How do I create a date stamp in a work sheet, if lets say anything gets revised or updated the date gets updated as well?
For Example: I want to put the date stamp in A1. So when anything gets revised in the document, A1 will have the date when that document was revised.
Here is my question: For every 128 ounces of raw paint we add 20 oz's of catalyst, 71.7 oz's of acetone, 20.5 oz's of IBIB and 10.2 oz's of MBIK. So for every 1 gallon (128 oz's) of raw we add 122.4 oz's of reduction. The total mixed paint used was 4.75 gallons. I need to find a formula to breakdown the 4.75 gallons (608 oz's) to find out the raw only. The reason I need a formula for this is because every part we run has completely different reduction rates so I need to be able to change those amounts. Im pretty good with formulas in excel but this one has me floored. I understand the total raw will be around 310 ounces but I need a formula to prove this.
View 2 Replies View RelatedI have a [COLOR=blue !important][COLOR=blue !important]worksheet[/COLOR][/COLOR] containing a large amount of [COLOR=blue !important][COLOR=blue !important]data[/COLOR][/COLOR] and then another worksheet made up of summary tables. The summary table uses SUM on various fields in the data to get monthly totals.
For example one cell is
=SUM(Data!C7524,Data!C7530,Data!C7536,Data!C7542,Data!C7548,Data!C7556)
Some of the other sums can be made up of 100+ cells.
I would like to be able to keep the sum in [COLOR=blue !important][COLOR=blue !important]the [COLOR=blue !important]cell[/COLOR][/COLOR][/COLOR] but somehow get a breakdown of what that sum is made up of and the description in the adjacent column. So I could [COLOR=blue !important][COLOR=blue !important]click[/COLOR][/COLOR] on the cell and ideally it would create a new worksheet listing all the criteria values and description for that sum, looked up using the references in the sum cell.
So clicking on the one above would give me something like
Description Value
JP7600 100.00
JS0140 300.00
JS0340 100.00
JS0540 50.00
JS0740 75.00
JS2030 100.00
Is what I'm after possible?
I know how to create a text link to other cells within a work book but how can I create a button which will do the same?
View 2 Replies View RelatedI have a source table that contains time information about shifts people work:
E
F
G
H
37
Source Table
38
name
date
start-time
stop-time
39
John
1-1-2012
13:00
15:45
40
Frank
15-8-2012
8:45
10:30
Now I'm looking for way to 'enhance the resolution' of this information to an hourly basis.
This means I would like to know how many hours John worked on 1-1-2012 between 15:00 and 16:00 (0.75).
I probably need to add 24 calculated colums to the table corresponding with the hours of the day.
I am trying to specify all files and folders in a directory and the only help I found was the following thread: Create Index Of Files In Folder That works really well but, what I need is to specify all the folders and all the files under a directory, does any of you awsome hacks have an idea of how I can enhance the macro if the thread posted above?
View 3 Replies View RelatedI was given this new task this year. Normally this process is done by hand, by one of the managers, but since it now belongs to me, I look at it as being a little to tedious to do. So I would rather have excel do it for me.
We have about 5 CSRs who handle clients based on the first letter in the clients name. I guess to make this easier to update in the future if the macro would respond to a cell reference for the number of CSRs that would be great. I will need to perform this task Annually, and of course each time a CSR leaves or we hire another one. This way work loads are kept close to the same.
I have a list of letters in column A. Each of these letters has a corresponding number of clients in column B. I have been given the task to try to split these values equally between the 5 individuals. I know that since the numbers don’t all add up and divide equally it can’t be done easily by hand.
I have attached an excel file with what I have in the sheet. Sheet 2 shows what I would like to have as an example as output on the same sheet. (What I put on the sheet is an example of the way I want it to look, are not close to adding up to a "close to equal" total.)
I am trying to calculate the the breakdown time for each hour in the factory. I have the start time and finish time of each breakdown. There are 8 hours in a shift starting at 6:45. So hour 1 would be (6:45-7:45), hour 2 (7:45-8:45). If the machine breaks down between 7:06 and 8:20 what will be the amount of time lost in each hour? In this example it would be 39 mins in hour 1 and 35 mins in hours 2. How I can achieve this result?
View 4 Replies View RelatedLet's say I have pivot data in the form of:
| Major Category | Minor Category |
toys | balls |
toys | puzzles |
toys | blocks |
clothes | shirts |
clothes | pants |
I want to loop through every minor category and display it and its major_category on a different worksheet. (I'm doing more than this, but for simplicity sake ...
How do I get a correct average of each supervisor from the data breakdown in columns f:g?
I was thinking an if statement like =if(a2:a10,"x" --then I am not sure how to say average of f:2:f10). Would that be on the right track?
*
ABCDEFG
1*****knowledgable*friendly*
2john*mike*dave*bill*lisa*5*4*
3john*mike***lisa*4*3*
4john***bill*lisa*3*2*
5*mike*dave*bill*lisa*2*4*
6john**dave*bill*lisa*1*4*
7*mike*dave**lisa*1*3*
8john*mike*dave*bill**2*5*
9john*mike*dave*bill**3*3*
10john*mike*dave**lisa*4*5*
I'm looking for a solution for my worksheet. I've got a main tab in which contains deposit data, I need to take the raw data format it and break it down into 7 different tabs. Each week a report is exported and then one for the actual month containing all the data. I’m currently manually recording some formatting macros because I haven’t learned to write my own yet. The number of columns in this report is always constant but the rows vary depending on the number of deposits. I have 7 tabs where the data from the main sheet will be broken down in. In my spread sheet I have the following headings. (A1)Date, (A2)Batch, (A3)Source (A4)Control (A5)Total Batch (A6)Total Variance
The information I would use to copy the rows to other sheets is going to be based on (A2).
Anything that starts with U65 would go into the U-65 tab.
1,7 or 8 would go to the lockbox tab.
2 or 5 = IN-HOUSE
3 = WIRES
4 = DATA OCEAN
Eh, WH, or WE = ACH
Finally the variance tab will contain any rows which have a number other than 0 Positive or Negative. My biggest problem is that I never know how many rows the data will have so when I recorded my macro I Started dragging my formats to about row 200. It could one day surpass this number so If I had a choice id set it to row 1,000.
How to make sure that every workbook I create has the option of "backing up before saving" turned-on? I need a Excel-wide option, i.e. one that applies to all workbooks created, The known to me facility of turning the option on every time I create a workbook is not satisfactory because often I forget to turn the option on when creating a new book -- and I create many.
View 2 Replies View RelatedI've got a master spreadsheet grabbing data from 12-13 different tabs/spreadsheets.
One of those has a table, and I was wondering:
Is it possible for the master spreadsheet to automatically add a new line every time someone adds a new line and creates a new entry in a different spreadsheet?
In other words,
If there is a table in the master spreadsheet, and someone adds a new line, is there a formula which allows the master spreadsheet to add a new line in order to autopopulate?
I have 2 types of data: Product & Store
And the data would go like this:
A
Corn Flakes
Frosted Flakes
Froot Loops
B
Store 1
Store 2
Store 3
Store 4
The number of entries of course is a lot bigger, that's why I'm trying to make it auto.
So what I need is to have a table created that would mix both data type in 2 columns, like this:
Product
Store
Corn Flakes
Store 1
[Code]...
How would this be done automatically by a formula?
I currently need to create around 1200 chart images using excel for a report series I am performing for a client. I have been trying all kinds of different things, all failures, to automate this process. My current method is to filter a pivot table on the building I am analyzing, copying the chart, pasting it as a picture in Ms Word, then saving that image as a Picture. Obviously, this process is quite lengthy, especially if I multiply that over the course of all 1200 images.
I am looking for a solution using a macro, vba, or a combination of things to automate this process and maybe even create the jpgs automatically?