Inserts New Row And Also Inserts New Sheet With The Rows Name That Was Inputted
Jan 8, 2007
(1) My main tab I have a macro button that inserts a new row and also inserts a new sheet with the rows name that was inputted. But on the main tab in cell A1 that is named, I can not get it to hyperlink to the new tab.
(2) When the button is hit to insert a new row and sheet I copy over previous tabs information and then I want to change two fields A1 and B1, A1 works fine but my B1 I can not get the formula to work correctly. My cell in B1 I need it to say 'DESCRIPTION: ' and then take the value of main tab 'Test Case Summaries' cell D?, it would be the new line and copy it here. See below... rname is the new tab name and lrow is the new row that was added when the macro button is hit.
t = Sheets.Count
Sheets(t).Copy after:=Sheets(t)
ActiveSheet.Name = rname
Sheets(t + 1).Range("a1") = ("Test Nbr " & rname)
Sheets(t + 1).Range("b1").Formula = "DESCRIPTION: " & "!$D$" & lrow
View 9 Replies
ADVERTISEMENT
Aug 22, 2009
I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.
Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.
View 3 Replies
View Related
Apr 11, 2014
I'm trying to make an order form that is based off of a price list. Basically there will be 200 items or so someone can just enter the quantity they want into a column. I would then like another sheet on the workbook to auto-populate all of the fields available. The thing I'm having trouble with is I don't want the finished form to be as long as the price list with blank rows in-between. I've been reading up to make a macro work for this, but have had no success.
View 3 Replies
View Related
Feb 1, 2009
How do I get some columns in sheet 2 to be a 'Live' copy of some colums in sheet 1? - 'Live' as in they include any changes to sheet 1, i.e. insertion/deletion of rows?...
I have set up a very basic shared workbook for me & four colleagues so we can all see what each others work loads are like and establish who has capacity for new work. We each have a sheet and record the work data in rows which details Name of case, Case ref no., age in days,etc... This then feeds a summary sheet displaying the totals of the relevant info and has pretty charts etc which we can print off for our manager.
As our cases are concluded we simply delete the rows. When we get new cases they are either added as new rows or inserted between existing rows (when similar work/related work). What I would like to do is give each of us a second sheet where we could record more specific detail regards each case. I would like sheet 2 columns A&B (detailing name name & Ref No.) to be the same as Columns A&B Sheet 1 - I have done this with a simple =Sheet1!A1...etc, which is great until any deletions/insertion are made to sheet 1 which then throws out sheet 2. How do I make Columns A&B for sheet 2 'live' copies of Colimns A&B Sheet 1?
View 3 Replies
View Related
Nov 30, 2009
need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
Rows("2:2").Select
Do
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select
Loop
View 5 Replies
View Related
Oct 26, 2008
Following is an example of an HLOOKUP formula I'm using:
=HLOOKUP(A1,$B$2:$E$5,4,FALSE)
The problem I'm having is that if a user inserts a row within the range B2:E5, then it throws my lookup formula out, because the formula should now be looking at row 5 and not row 4. How do I get the number 4 in the formula to be a variable that always selects the last row of the specified (variable) range?
View 3 Replies
View Related
Jan 9, 2009
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
View 9 Replies
View Related
Jan 29, 2014
If the user inserts a row, I want all formatting/formulas/data validation from the row above to be inherited in the newly inserted row. I want this to occur on sheet1.
When you insert a new row, although the entire new row is selected, the active cell is the cell in column A. So I figured I could base a routine off of the active cell. Here is what I tried:
[Code] .....
I am trying to reference a range. The first cell in the range is above the active cell. The second cell in the Range is above, the active cell, then to the right of the range, then down one. By referring to this Range, I am then trying to use the FillDown method. This code isn't doing anything.
Also, I need Excel to automatically run this code when the user inserts a new cell on sheet1. Would I need an event handler or just put this code inside of the Sheet1 code window?
View 2 Replies
View Related
Feb 4, 2009
i am trying to do something along the lines of. a For Next Loop that selects the cell N6 and inserts the formula.
$K$7*F6+$L$7
then fill down to the cell N754
then selects O6 and inserts the formula
$K$8*F6+$L$8
then fill down to cell O754. repeating this until there are no more values in either the K or L column. in the attatched spreadsheet i have deleted the values from 50 something down to 754 but the idea is still the same.
so that each formula inserted keeps the cell from column K and L constant when filled down but F changes, then when the next column is used(ie. from column N to O), the next row in the K and L is used but F starts from F6 and changes as it is filled down again.
View 5 Replies
View Related
Oct 21, 2007
#17. Create a macro named "AddSale" that perform the following tasks:
-Switches to the Sales History worksheet, and then inserts a blank record in the third row of the Sales History worksheet, shift the rest of the records down.
i did create the Addsale to the macro i use Tools-Macro-record a new macro (is that right?) and i switch to the sales history worksheet insert a blank row. (am i right?) it state inserts a black record, i can't find record anywhere so i assume its blank row.
View 5 Replies
View Related
Dec 10, 2008
I'm trying to write some code that inserts a SUMIF function to a given range of cells:
In simplified form, the code runs as follows: ...
View 9 Replies
View Related
Mar 26, 2009
I have cobbled together (borrowing from examples I have found online because I don't know VBA) a form which inserts a date into a spreadsheet.
The trouble I have is that this date field is in USA date format and I need UK.
If I enter 03/01/2009 into the form, it appears in the spreadsheet as 01/03/2009. I have formatted these cells to display the month only and because of the way it was entered, the next column displays MAR instead of JAN.
Can I validate the input value in a form?
View 7 Replies
View Related
Mar 24, 2013
I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.
The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.
View 7 Replies
View Related
Oct 20, 2009
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
View 5 Replies
View Related
Dec 13, 2013
In this example on Sheet 2, I'm trying to hide rows 1-6 if 'E6' is 0, and hide rows 15-26 if 'E18' is 0. These cell values are equal to the same cell in Sheet 1.
I've tried different codes I've found online, but I'm not very experienced with VBA so I can't get it to work. I can get rows 1-6 to hide, but it doesn't do it automatically whenever a zero is inputed into Sheet 1. It only works if I enter something into another cell on Sheet 2.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E6") = 0 Then
Rows("1:6").Select
Selection.EntireRow.Hidden = True
[Code]...
View 5 Replies
View Related
Feb 2, 2009
I need to sum all the rows, except the header row, of a column inputted by the user. The column will be located in a worksheet dubbed SourceSheet.
Here's a few pieces of code related to what I am trying to do. The header row is always row 1. So the rows I'm summing are always 2 - myrows.
View 6 Replies
View Related
Dec 19, 2006
I need to have a User input a date via an InputBox, and then based upon that date, delete rows based on values in Column G. For instance, if a user inputs 12/25/2006, I need to delete all rows that have the date that is less than 12/25/2006 in Column G. There will never be more than 500 Rows of data, and there is data in Columns A thru Z
View 4 Replies
View Related
Jun 21, 2014
I'm looking to create a macro which displays a user entry box where the user can input a starting number and then input an ending number. The macro will then cycle through that range inputting each value into a specific cell and perform a print function. It will continue doing this until the last number in the range.
E.g.
Min. Number inputted = 1
Max. Number inputted = 80
Macro will not cycle through that range:
Input 1 into Sheet1 Range A1
Print Page
Input 2 into Sheet1 Range A1
Print Page
.....Continue doing this until it reaches 80
View 3 Replies
View Related
Mar 21, 2007
I have got the task of doing the results for a local athletics mid-distance event once again and im fed up of doing it the present way. Basically i have a sheet which i input the race registrations which has the following collums: Number, Name,School,
and i have another sheet for results which have: Position,Number,Time. I then have to merge the two in a magical way and i get the results. However what i want to do is have the first sheet the same but the results sheet would be different reading: Postion,Number,Name,School, Time
But when i input the number, it will check with the other sheet and automatically fill the rest of the boxes (bar position and time) hopefully. Using my knowlegde i gathered at college(fairly average knowledge of vb) i still cant get it to work the way i planned. Is it actually possible to do, or should i go to my pet hate access?
View 2 Replies
View Related
Dec 26, 2011
On my soccer spreadsheet, I currently have a sheet for entering results, and I have a separate sheet where all the match data is housed. Below shows screenshots of this:
This is the sheet where the user can choose the teams, the season and enter the result of the match. In this example, Arsenal v Aston Villa has been selected from the drop down menus, and on the far right shows the result of the match (ignore the prediction result, not relevant). The user is able to change the result of this fixture using the 'Enter/Change Result' macro button.
The userform used to change a result:
The sheet where all the match data is housed:
So when the user changes a result, the data gets transferred to the results sheet above.
The problem is, as things stand, the user CANNOT enter new results. They can edit current results between two teams for any season in which a game has been played.
This is why I would like to know how to be able to do this. What this would involve is the user inputting a new result between any teams, and the data they enter within the userform getting transferred to the results data worksheet. Basically, everything revolves around the concatenated references which contain the team reference numbers and the season. A new row would have to be created for a new result.
For example, if I wanted to enter a result for Arsenal v Aston Villa for season 2011/2012 (not yet played), the user would select the relevant options in the drop down menus, enter the result, season and date - then that data would be dumped in the results worksheet within a new row in the right place. The data in the 'Lookup' column of the results sheet would need to update accordingly, so that the most recent to the oldest fixture is in chronological order (1v2-1: the '1' means the most recent fixture, currently that is present for the 2010/2011 fixture but would need to be updated to '1v2-2' if the new result is inputted.)
View 1 Replies
View Related
Mar 8, 2009
I had already asked a question like this on the forum and was unable to get a working answer. I'm still grateful at the time JBeaucaire spent trying to help me. The solution we had worked in Excel 2003 but when moved into 2007, an error came up. To see the solution and the error, the previous post is at
HTML http://www.excelforum.com/excel-2007-help/663593-adding-function.html
What I'm trying to accomplish is if a person inputs a number into a cell, A1, the value will be added to another cell, A2, then A1 will be cleared. If another value is inputted into A1, then it will be added to the value into A2 on top of all the other values inputted and clear A1 again.
View 7 Replies
View Related
Oct 24, 2013
I am creating a 'add supplier' userform, in which i open the userform, type in the new suppliers information, then press submit.
The problem is that the data i enter deletes the data that i previously entered into row 26 instead of posting the data into a new row (I wish it to be 27, then 28, etc)
Here is my current code for the userform
VB:
Private Sub CommandButton1_Click()
nextrow = 26
Sheets("Suppliers").Cells(nextrow, 3) = UserForm5.TextBox1.Value
Sheets("Suppliers").Cells(nextrow, 5) = UserForm5.TextBox2.Value
Sheets("Suppliers").Cells(nextrow, 6) = UserForm5.TextBox3.Value
Sheets("Suppliers").Cells(nextrow, 7) = UserForm5.TextBox4.Value
[Code]....
View 2 Replies
View Related
Jun 18, 2013
I am making a list in excel and there are multiple cells where I want the user or customer to fill in their information. For example, In C2, the customer is supposed to enter the name in. How I can put text in this cell that will automatically disappear once they start editing the cell?
i.e. [Manual Entry]
View 3 Replies
View Related
Sep 16, 2011
Is it possible to lock a cell once data has been inputted into it??
I want to make a spreadsheet which has empty cells for reference numbers that once entered cannot be changed unless it is by the administrator.
i also want the spreadsheet to be accessed on multiple pc's by multiple users.
View 9 Replies
View Related
Jun 18, 2013
I am making a list in excel and there are mutliple cells where I want the user or customer to fill in their inormation. For example, In C2, the customer is supposed to enter the name in. How I can put text in this cell that will automatically disappear once they start editing the cell?
i.e. [Manual Entry]
View 1 Replies
View Related
Nov 10, 2008
1. Is it possible for this hyperlink box to only be displayed when ALL the questions have been answered? i.e. All drop down boxes have been used?
2. Is it possible for the result (along with their individual names and scores) to be saved on another sheet? This of course would be supplemental (i.e. When the next person finishes the test, their name would be next on the summary etc.)? I would then have a whole list of who had taken the test...when...and what their score was!
3. Finally, and I know I'm pushing my luck, but is it possible for this summary sheet of names and scores to be hidden?
View 9 Replies
View Related
Mar 14, 2014
I have created a workbook to input, store and part edit defects on plants and machinery by userforms. I have everything working except either picking up a number I have pre-loaded into a column or auto generating a number onto the inputting userform. I need a number in the userform to be either generated in the next empty row in column I or taken from the numbers in the next empty row I have loaded into column I. Password is snatch.
Input 2003 test1.xls
View 3 Replies
View Related
Apr 7, 2007
I have a Workbook that has 2 worksheets, one called CALL QUERY and one called CALL LOG. On the CALL QUERY sheet, cell D9 is a user inputted cell with the cell validation set to list. The user uses the dripdown list to pick an office identifier (3-Letter Code).
On the CALL LOG sheet, I have info about individual calls. Column E has the 3-letter office identifier for each call.
I am trying to find a way to automatically auto filter the list on the CALL LOG sheet with the user input in cell D9 on the CALL QUERY sheet.
I was given the following (N2 is a cell on the CALL LOG sheet that equals the user inputted cell D9 on the CALL QUERY sheet)
View 14 Replies
View Related
Mar 25, 2009
I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.
View 4 Replies
View Related
Nov 20, 2013
EXPENSE MASTER 2013 sample color.xlsx
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
View 3 Replies
View Related
