Creating New Sheets With Macros Based On Column Information
May 9, 2012
I am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do
-In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11)
-I want to create one for every number in the column
-Then I would like to copy my 'Template' tab to each new tab.
-From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom'
-Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
View 6 Replies
ADVERTISEMENT
Feb 19, 2007
I have a wb containing several sheets that the field supervisors use to fill out for ordering material. Each sheet contains different types of material. The problem is that, if they only need one particular item from each type (sheet), they have to print 7 or 8 sheets just for those 7 or 8 items. I want to create a sheet that will list everything they are ordering in one compact, neat area, so they only have to print one sheet.
The set up of the sheets goes like this. There's a column containing the name of all the items. Next to each item is a place they can specify how many of that item they want. They leave it blank if they don't want any. That's it. Really quite simple.
So now, I need this new sheet to find all the cells that a quantity was entered on the other sheets, and list that and the description of the item wanted, along with what type of material it is. I have figured out one way of doing it, but it would take a crap load of hidden IF formulas, and I know there has to be a simpler way. I'm open to all suggestions - whether it's vb codes or formulas, it doesn't matter to me.
View 9 Replies
View Related
Jun 14, 2007
I have a spreadsheet that I am trying to use as the 'master'. I want to break down this master by category (Column D) and label the worksheet by category (AA, Code, SQL...)
I need for the code to search the D1:D500 column and find "AA", "Code" or "SQL" and place the entire respective row on each respective worksheet. Then sort by colmn E. There are columns a:h that need moved to each sheet.
View 3 Replies
View Related
Jan 27, 2014
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
View 1 Replies
View Related
Mar 26, 2014
I would like to generate a dynamic list of employees on sheets based on information on Sheet A. Here is my workbook structure:
-Sheet A contains all employees information (Name, division, salary hire date, etc)
-Sheet B is named Div A
-Sheet C is named Div B
-Etc...
Here is what I want to happen:
-Sheets B should pull the employees name and salary from sheet A only for employees of division A
-Sheet C should pull the employees name and salary from sheet A only for employees of division B
-Etc..
Sheet B, C , etc. should update the info and resort every time sheet A is updated.
View 5 Replies
View Related
Jan 15, 2013
I have excel file in which each sheet will be filled by a different clients (Client1, Client 2, Client 3.. Client n).
Each sheet will have two columns "Description" and "Quantity". Client will add the descriptions and quantity.
Apart from the sheets filled by clients, the file will also have a sheet called "Summary".
After all clients filled, I need all distinct "Desciption" and "Sum Quantity of that Description" in the "Summary" sheet.
[URL]
View 1 Replies
View Related
Jul 8, 2013
I have a database of information on which one of the columns is supervisor. I need to put each supervisor and associated employees on separate sheets. How do I do that?
Column Headers
Last First Title Supervisor Rating Box
Need the supervisor column broken out on a sheet with all employees listed in the data base.
View 6 Replies
View Related
Dec 11, 2013
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
View 1 Replies
View Related
Apr 23, 2008
Am trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.
View 9 Replies
View Related
Jul 8, 2014
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
View 3 Replies
View Related
Jun 1, 2006
I have a workbook with many sheets, all of which derive information from the first sheet. On the first sheet, I enter values from say J1:J29. Each of these values is fed into one of the subsequent worksheets. I would like to have some code that would look at my range, determine which cells are used and print the corresponding worksheets. Write now I have a bunch of If statements that say
If Sheets("Worksheet").Range("j8") > 0 Then
Sheets("698-ALY W1222").PrintOut
End If
the problem is that it takes to long as each statement is evaluated and the print command sent.
The values in column J have names in column I and while the names do not exactly match the worksheets, a person could look at the name and match the correct sheet. For instance the name in column I that corresponds to the worksheet name 698-ALY W1222 is 698/ALY.
Thanks
Bryce
View 9 Replies
View Related
Oct 29, 2008
My workbook contains several worksheets each recording the results of equipment tests - one sheet for each piece of equipment. Rather than open each worksheet to check the date of the last test, other 'overview' worksheets pick up and display the last test date from each equipment record using the formula =MAX('sheetref'!A15:A500). Is there a better formula to do this?
Some items of equipment are tested at more than one frequency, ie Monthly, Yearly, etc. Therefore, the entry on row 15 of an equipment record may be column A 28/10/08 column D 'M' and row 16 column A 29/10/08 column D 'Y'. The above formula will only display the last test date, irrespective of frequency. There is a seperate overview sheet for each test frequency therefore, I need to modify the formula so that the date it copies from the equipment record worksheet to the overview worksheet is the date of the last test for the specified frequency.
View 9 Replies
View Related
Jun 24, 2009
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
View 9 Replies
View Related
Oct 12, 2011
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
column CColumn Fcolumn Hcolumn J
Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....
View 2 Replies
View Related
Nov 10, 2008
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
View 14 Replies
View Related
Dec 14, 2011
This challenge may be better handled in Access or a true database but the person making the request only uses Excel.
1. Create a form that will accept names, dates, and grades.
2. Store the information on a separate worksheet so that it's being accumulated and reports can be ran from it.
I'm aware of Excel's automatic form but it doesn't allow for validation.
Example: some score ranges are 0-5 others are 0-15. We can't allow for a 12 to be put in where the max is 5.
I know how to do the validation. The big challenge now is telling Excel: I've completed entering this record. Now clear, and start a new record on the next row?
View 2 Replies
View Related
Jul 28, 2014
I have a userform that searches a data base for an address an pulls up the corresponding information. I was wondering if there was a way to get it so that if I put 1234 5th Street when it puts the information into the data base it would separate it into two cells so 1234 would be in one section and 5th street would be in another.
View 1 Replies
View Related
Oct 27, 2013
I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.
A B C
1 Fruit Oranges
2 Veg Lettuce
3
Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts
Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts
View 3 Replies
View Related
Aug 17, 2007
I'm trying to do is create a macro that will autopopulate information from a source sheet. What I have is a different route sheet for 25 or so technicians within my company on one worksheet.
I want to be able to have a source sheet that I can change daily and have it autopopulate the information needed into the appropriate cells/sheets from this one source sheet.
I am pretty sure there is a way to do this, but I'm not familiar enough with creating macros to do this myself, although if I had an example of one cell and a simple explanation of how to fit this to the information needed I think I could do this on my own.
View 9 Replies
View Related
Jul 11, 2002
I would like to create a macro which will copy the names of the files and folders of a chosen directory into a spreadsheet, effectively creating an index. I don't know how to go about this or even if it is possible. I'm trying to avoid having to do it manually as the directory in question spans 1500 folders...
View 9 Replies
View Related
May 2, 2008
Am a newbie in excel macros. pls i need assistance as to how to create a macro that searches a worksheet for a particular word. i wrote something like below but am having problems in it.
Private Sub CommandButton1_Click()
Dim word As TextFrame
If word Is Not Empty Then
For word = "aaaaaaaaa" To "zzzzzzzzzz"
ThisWorkbook.Worksheet.Find
Next word
End If
End Sub
View 9 Replies
View Related
May 7, 2014
I am trying to get a grip on my gas n electric bills. My problem is in creating a formula to convert the info I have into the final bill.
The info I have is -: days of use, amount of G n E used, standing charge, price/unit and discounts applicable. For simplicity I have converted them all into a rate per day and £GB. I tried the following formula
:-(standing charge per day * days used)+(cost per KWH * No.of KWH used) - (discounts * days used) / (100) * (94)
This last one is to account for a direct debit 6% discount. I used cell address for things like No of days usage and KWHs used in the period. The values in these cells have themselves been calculated via a formula from other info. The results I get seem to be a constant variable which indicates that I am doing it wrong.
View 3 Replies
View Related
Jan 6, 2013
I want to create a custom tool bar for a work book but when I right click in the unused part of the tool bar area the only options I get are customize Quick access tool bar and customize the ribbon.
View 3 Replies
View Related
Jun 11, 2013
I have a dynamic set of data, loaded from a server. The columns are FIXED. I will attach a sample of how the data looks.
site_code
report_goup
asm_week
[Code].....
I don't know how to attatch a file here, but the first row is the title row, under it are the values. The columns here are fixed. What i NEED to do, is somehow create something that will make a seperate graph for every different PLATFORM , showing the FPY chaging over time. Time here is the weeks which will always be a rolling 12 weeks. I'm thinking that i need to use MACROS to do this, but i'm not sure where to start.
View 2 Replies
View Related
Apr 20, 2012
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies
View Related
May 14, 2014
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
View 2 Replies
View Related
Aug 15, 2008
I currently receive a download that contains multiple reports in one excel sheet. Each report is separated by the value "Vendor" in column A. I would like to separate each of the reports into a new excel sheet. Basically, I will need to copy all rows between "Vendor" and "Vendor" values and paste to a new sheet.
View 2 Replies
View Related
May 5, 2006
I have a huge db that has a column "C" with a list of names. I have been creating tabs and naming them according to that list. I wonder if there is anyway I can create and name the tabs automatically. I already searched for threads in the forum, but only found this this backwards "tabs to cells". By the way, the names in column "C" sometimes repeat.
View 2 Replies
View Related
Apr 16, 2008
There are 2 sheets in the excel worksheet. in one sheet there would be drop downs
the 2nd sheet will have 4 columns A,B,C,D) Depending on the selection in sheet1. If a1 = Y then columnA in the 2nd sheet should be hidden. If a1 = N then columnB in the 2nd sheet should be hidden. how can this be done..The key is the columns to be hidden are on a different sheet and that is where I am having the difficulty.
View 3 Replies
View Related
Oct 4, 2013
How to pull data based on column B to individual tabs.
I have the following data:
A B C D
Jane Doe | L1 | 20% | High
John Doe | L2 | 15% | Medium
Mike Smith | L1 | 60% | Low
Marie Smith | L4 | 10% | Low
I want to have the first tab/spreadsheet only pull records that have L1 values for column B
In the next tab/spreadsheet I want to pull records that have L2 values in column B etc.
What formula can I write that will pull all L1 records (A.B.C.D) in to Tab 1, then all L2 records (A,B,C,D) into the next tab?
View 10 Replies
View Related
