Auto Insert A Row With Info Populated From The Row Above
to be able to do is have a stock control sheet that tells me how much of 1 item we have in stock which is easy, the hard part comes when this stock is sold as this stock can be sold to a number of different customers e.g.
We have 10,000 X Pens
Customer 1 buys 1,000
Customer 2 buys 6,000
Customer 3 buys 3,000
i need a way of saying that we have 10,000 pens but if we sold 1,000 then we can click a button (macro maybe) and that will automatically see that we have 10,000 for that line and we have only sold 1,000 so we need a line inserting saying that we have 9,000 remaing and so on.... The sheet needs to record who these goes to though, that the reason i was thinking of adding a row
If this doesnt make sense then i am happy to answer your questions?
i can upload an example excel sheet if required if someone can explain how i do this?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automatically Insert New Row For New Info
The spreadsheet is used to keep track of shares that are purchased / sold It will make all tax calculations as well as provide a single statement showing all transactions rather than having to sort through dozens of individual statements. I seem to have most of it working but could use some help with the following. I would like to be able to have a spot (maybe on a separate worksheet) where you could enter the four items from which everything else is derived, share price, date, number of shares and whether it was a purchase or a sale, and have it automatically: 1. add a new row (complete with formulas and formatting info) to display the new information 2. sort the new data to fit into the right spot in the spreadsheet by date.
View Replies!
View Related
Retain Auto Number Sequence After Row Insert
I'm using a simple auto numbering command. eg: cell A1: 1 cell A2: =(A1+1) then shift, copy/paste cell A2 until A600, This creates a sequential number column from 1-600. My problem lies when a new row is inserted, which is left blank. How can i maintain this auto function upon a new Row insertion?
View Replies!
View Related
Insert Row At Specific Location And Auto Fill Formulas From Above
What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg). Sub ChkDates() Dim c As Range Dim DelRng As Range Dim ArcRng As Range Dim i As Long Dim l As Integer Application. ScreenUpdating = False Worksheets("Report").Activate For i = 60 To 8 Step -1 Set c = Cells(i, 33) If IsDate(c) Then ..............
View Replies!
View Related
Identify Button (shape) Row To Insert Row And Delete Row
I have a button (group containing and add and delete button). I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row). Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row. This will allow me to add/insert rows by the button located at that row The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp) I also note that when a group is copied, it has the same shape name as that copied. I want to keep the add and delete shape within the group (BtnGrp). I do not want to select a cell or row or enter a row number to delete etc. refer to sample workbook attached. Currently only has one record row.
View Replies!
View Related
Formula To Find Last Populated Cell In Row
i need to find the last populated cell in the row say I9:CN9 and return the description for that column the resides in I5:CN5 and then fill the formula down for about 500 rows, just not sure even where to begin, do i need a custom formula in VBA? as i can't seem to find a combination that works built-in...
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 – is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Macro To Format The Auto-Sum Row Without Knowing What Row# It Will Be
I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically... 1. Merge & center F & G in the Auto-Sum row with the text "Totals". 2. Change the row height of the Auto-Sum row to 44 pixels. 3. Center the vertical alignment of the Auto-Sum row. 4. Put the thickest border around the Auto-Sum row in cells A-K.
View Replies!
View Related
Auto Change Row/font Colour And Move Row
Attached example sheet. Basic table of data, with column F being a validation list, is it possible that when choosing COMPLETE from the list, the row and font change colour, and then move to the top of the list? I'm not the only simpleton using the sheet so I need it to be as simple as possible. I know it doesnt seem like a big thing but the sheet we are working from is huge and I dont want people cutting and pasting away, I just dont trust them with my spreadsheets.
View Replies!
View Related
Insert Row Syntax - Resize Row VBA Error
I need to insert rows in row A44 depending how many cells exits in a range i named "ALL_C" I saw this code that inserts 10 rows on A44 so i thought by modifying it the way i did would work but it didn't. I'm still new at this stuff. What am i doing wrong? Original Sub Insertinrow43() Range("A44").Resize(10, 1).EntireRow.Insert End Sub Modified (doesn't work) Sub Insertinrow43() Range("A44").Resize(Count(All_C), 1).EntireRow.Insert End Sub Thank you!
View Replies!
View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1). The inserted row is changeable and is determined by whichever is the current active cell. Eg: Active cell is something random like E16 I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
View Replies!
View Related
Insert Range-named Row At User-selected Row
On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet. If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position). This is what I have tried so far:
View Replies!
View Related
Lookup Column Header Value When First Cell Is Populated In A Row
i have a manpower chart with months along the top and names down the far left. People are going to start work in different months so i want to know at the side of each persons name the month they start. Does that make sense? Heres a simple example I have column headers starting at B1 thru F1, with the following values Month1,Month2,Month3,Month4,Month5 In cell D2 i have the value 1 I want Cell A2 to return the value Month3 Whats the formula i need to input in A2
View Replies!
View Related
Insert Row And Copy Cell From New Row
Attached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?
View Replies!
View Related
Goto Next Row Of Info
i have this code that gives me 1st row of data , from Row 10 of every sheet in workbook, how can i modify to give me row 11 ,12,13,14,15,16 as well upto row 21 if there is Data in Col C ( max range is C10:C21) , For intX = 1 To Sheets.Count For intY = LBound(arySkipSheets) To UBound(arySkipSheets) If Sheets(intX).Name = "MachCapRpt" Or Sheets(intX).Name = "MachAdSht" Or Sheets(intX).Name = "Times" Or Sheets(intX).Name = "MachSchd" Then Else .Range("A" & intNextRow).Value = Sheets(intX).Name .Range("B" & intNextRow).Value = Sheets(intX).Range("B10") .Range("C" & intNextRow).Value = Sheets(intX).Range("C10") .Range("D" & intNextRow).Value = Sheets(intX).Range("E10") .Range("E" & intNextRow).Value = Sheets(intX).Range("H10") .Range("F" & intNextRow).Value = Sheets(intX).Range("M10") .Range("G" & intNextRow).Value = Sheets(intX).Range("W10")..............
View Replies!
View Related
Recalling Info Into Another Row
I am trying to recall info from the rows in another part of a worksheet when I type the first column. For example, I have over 300 rows of: Apples 155 12oz red Orange 120 10oz orange Banana 50 11oz yellow etc. I want to select or type only the word: Apples And have the rest of the columns fill in with the rest of the info, like so: Apples 155 12oz red A filter would work well to select, except I need about 7 in the worksheet as it is a form and excel only allows one. I can’t seem to make the advanced filter work. An IF function would work, except I'd have to put all the words to match in " " marks, which would be very time consuming. how I can recall the info from my list to the 7 lines I need to copy them into?
View Replies!
View Related
Insert Row Then Copy Row Below
The posts I have found regarding inserting rows and copying formulas etc. and their answers do not seem to work in my worksheet. All I would like to do is insert a row then copy the row below into the new row. I have tried to modify the VBA given in similar posts and cannot seem to make it work.
View Replies!
View Related
Insert Row And Copy From Above Row
i have a excel spreadsheet named "ECN Number". in this sheet i have a row in which column5 marked with value "x",column22 is empty " " and column23 has "some value". if the above statement is true,i want to insert an entire blank row and i want to insert another row below this row below the empty row with column6 marked as "x",column22 empty " " and column23 with the samevalue from the above row. after inserting this row with these values, i want to insert another entire blank row. i have attached the workbook with the specified sheet.
View Replies!
View Related
Find Last Row Containing Value, Then Insert Row After
I am creating a worksheet of contacts and the date and time that I phone them, along with their current "status" in our company. I have created a form and have got it to do the insert of new data that I input, BUT What I would really like is enter a "client number" (which is unique) and have it search my table for any record(s) with that client number, and then show me the last record that matches the client number, (this can all be done from within my form) then If I click on the "insert" command button, I want it to insert a new row after the current row and move my form data in the form into the new row which will include a date and time. So, To summarize.....I need 2 similar subroutines. LOOKUP ----- I need to have my form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches, then display the data or display nothing is there is no match. INSERT ----- I need to have the form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches and then insert a blank row here and move my data into it.
View Replies!
View Related
Autofill Only If Adjacent Row Has Info...
Below is my current code. I created this to work for a spreadsheet I had. Works mint. Then I use in another spreadsheet which is almost identical but has less rows. Everything works, but then im left with a bunch of extra rows that have DIV error. Rather than select range 2:34, is there a way to select the entire column, and autofill only if the column next to it has a number in it?
View Replies!
View Related
Sort Info In Column Into 50 Row Blocks
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here... 2 2 2 2 33 33 33 4 4 4 4 4 4 etc. Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no. so that it looks like this 2 2 2 2 46empty rows here.......................
View Replies!
View Related
Highest Number In Row Then Info 23 Lines Down
I am attempting to pull the Highest number out of a row of cells. Then grab the info 23 lines below. What I have is a spreadsheet that pulls from other spreadsheets and tracks all of my sales for the year. This is divided up into tabs for each day of the week. I am a formula to find the highest sales for Tuesdays {LARGE(Tuesday!B8:BB8,1)} then in the cell below it I need it to show the date that days sales happened on. That date is listed 23 lines lower. I have tried using {HLOOKUP((MAX(Tuesday!B8:B88)),Tuesday!B8:B88,23)}. This kinda works. It pulls from the 23rd line but only from the first column. Not the column that has the highest sales. I have searched for a solution but I must not be typing in my question correctly.
View Replies!
View Related
Insert New Row Above A Row
How can I insert a row immediately above a row containing the term "Score" in column B that is say cell B9 contains the term "Score" then on running the macro it will insert a row at row 9 and will pull down the row containing "score" to row 10 and so on. Can a macro to this be provided?
View Replies!
View Related
Copy The Information From The Row Directly Above The New Row And Paste (values, Formulas, Formats, Etc) Into The New Row
1. In whatever cell is selected when the macro is run, enter a new row. 2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row. 3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc. I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
View Replies!
View Related
Look-up And Insert Row
In column B, there is a list of companies. In column C there is the sales value for each company with sub totals after each type of company. For eg: B3 - Header 1 B4 - Company name B5 - Company name B6 - Sub Total B8 - Header 2 B9 - Company name B10 - Company name B11 - Sub Total What I need to do is create a macro to look down column B for the cell containing "Sub Total" and insert a new row above. I will create a button to do this. So, if I want to add a new company under header 1 I will press button 1 and a row will be inserted above the header 1 sub total. The same will apply if I want to add a new company under header 2 and so on.
View Replies!
View Related
Auto Highlight A Row
I'm trying to reduce my formating time by have a script auto highlight a row based on a certain value in column "K" SPECIFICALLY K9 thru K1009. Hight Light Row - YELLOW if this value "AAIT ASSY" OR "AAIT OW" is in column "K" SPECIFICALLY K9 thru K1009 Hight Light Row - LIGHT YELLOW if this value "AAIT KIT" is in column "K" SPECIFICALLY K9 thru K1009 Hight Light Row - TURQUOISE if this value "C-TYPE" is in column "K" SPECIFICALLY K9 thru K1009 Hight Light Row- TAN if this value "C/A" is in column "K" SPECIFICALLY K9 thru K1009 Hight Light Row- ORANGE if this value "ZZ" is in column "K" SPECIFICALLY K9 thru K1009
View Replies!
View Related
Insert Row Prompt
I'm in search of a way to prevent people who use a spreadsheet of mine from screwing up formulas. I've hidden rows 10 and 12 in a spreadsheet. i have a TOTAL formula in a cell on row 13 that counts from 10-12. that way, when people insert rows, the total is always right. problem is, people somehow seem still screw it up. I'm hoping there's a way for me to put a button on the sheet to insert rows. and when the button is pushed, be promted to enter a number of rows. then have it insert that many rows. Is that feasable? Thanks!
View Replies!
View Related
Insert Row Macro
I would like to find the code to make a button in excel, that when clicked, will insert a new row into the last row of a section so that the formatting is the same as a sample of other rows in that section, but it will be clear of data and only contain the correct formatting. I would also like this code to make a sum formula in a cell below the newly inserted row to change and to include the new cell within its formula. For example: I have a form on excel that has space for 6 rows of data with 3 pieces of information (which are: item, section, cost). If / when I need to add a 7th line I have box that I would like to assign a macro to so that when I click on it, it adds a 7th line with the same formatting that can be found in rows 1-6 but will be void of data. However, under the 6th row I have a total of the 'cost' with an '=sum(D7:D12)' formula and any other code I have found and have used it does not automatically tell the formula to add an additional cell to its summation to make it =sum(D7:D13).
View Replies!
View Related
Insert Selection To Next Available Row +1
I have a table in rows 19:39, and I have assigned this macro to a button. If a new table needs to be inserted "just press the button", and I would like it to insert a new table below the last table created. So, I thought it was Selection.Insert Shift:=xlLastRow + 1, however, this just seems to push down the previous table created by pressing the button. (I have more code that only formats the table after insert, but am not showing it here, since for testing I have 'd it all out). Thanks for any help in advance. Rows("19:39").Select Selection.Copy Selection.Insert Shift:=xlLastRow + 1 Application.CutCopyMode = False
View Replies!
View Related
Insert Row With Button
I have a button on a cell that is attached to a macro that inserts a row above it. I have to manually select the cell that the button is on, or any cell on that row, first to get this to work. I could use some help in getting the macro to select whatever cell, or row, that the button is on so this will work correctly.
View Replies!
View Related
Insert Row On Every Sheet
I have 200 sheets on a workbook if i try selecting all sheets as a group or then even insert a row it takes ages and screen goes white.. Can anyone help me with quicker way with a vba code where by i could insert row on Row 1 & Row 3 on every sheet of workbook.
View Replies!
View Related
Automatically Insert Row
problem with Excel and I have no idea how to solve it. Hmm, how do I explain this? Someone is giving me data pulled from a server in an Excel format. I have a column that has a series of entries. For example: Blank Entry 1 Entry 2 Entry 3 Entry 4 Entry 5 However, sometimes there's an entry or two missing. So it'll look like this: Blank Entry 1 Entry 2 Entry 4 Entry 5 Is there anyway to tell Excel to say "Hey there's an entry missing here," and have it insert a row with the missing entry value? I do not work in Excel ever but people around here do, in case someone has some advice. I already asked the guy if he could just server me the data formatted correctly, and -- of course -- he can't/won't.
View Replies!
View Related
Insert Row Using Macro
I hav a macro to insert a new row. But i also want the macro to ask the user to input where he wants(which row number) to insert the new row?? For example when i run the macro, an inputbox should come up asking to input the row number. The user should be able to enter the row number (take 10 for now). And Excel should automatically insert a new row in row number 10. So the previous row 10 now becomes row 11.
View Replies!
View Related
Insert Row At Each Change Of Value
I have a set of data TimeABC 110000 3802010 5802010 9602010 11603030 13404030 15204050 I want to run a macro that add a row if the values in A,B, or C change. Example, from time 1 to time 3, the value in both A,B, and C Change. I want to add a row between those 2 rows. TimeABC 110000 310000 3802010 I also want to add a row if only one of them is changed. Example is between time 5 and 9. TimeABC 5802010 9802010 9602010
View Replies!
View Related
Copy Row And Insert Above
I have a list of names in Col A. These names are seperated by their respective department headers also in Col A. Rows with (Add Member) contain all the formulas and formating necessary for that perticular department. I need to copy the (Add Member) row and place it above the the old (Add Member) row, then clear the contents of the A"#" cell to allow the user to "Add" the new member to the list. I would like this to occur only when Add Member cell is clicked in some way. The (Add Member) cell is locked to prevent deletion in a protected sheet. This new row will have to be unlocked for the new entry. A1 Name Department A Joe Smith Ann Doe John Snuffy (Add Member) Department B Jon Doe Frank Lee (Add Member) Department C Etc. (Add Member) ActiveCell.EntireRow.Select Selection.Copy ActiveCell.EntireRow.Select Selection.Insert Shift:=xlDown
View Replies!
View Related
|
TRACKER
