Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert Row
Hi,
I am a beginning user with very limited knowledge of macros. I am looking to add the following functionality. Presently I have a spreadsheet with enough room for 32 data input rows before I sum two columns' results (columns D&F in rows 40 and 41). Obviously, as time goes by, more rows will be needed for additional input. I would like for the user to, upon pressing the enter key at the last cell of data input for that last data row entered, to have excel automatically insert a new row immediately below and move the cursor to column A for new input. Is this possible without adding too much complexity? Thanks for all the assistance!!! Pete |
#2
|
|||
|
|||
Pete
I'm not too clear on what you mean to say. You say you want to sum in 2 rows (40 & 41). This doesn't seem right. Anyway I made some assumptions about what you want. The following 2 macros do something like what you are asking for. We may need to fine tune this after you see it. The first macro goes into the sheet module of the sheet that holds your data. Right-click on the sheet tab, select View Code, and paste this macro into the displayed module. This macro is triggered by any change to any cell on the entire sheet. The code in the macro stops anything from happening unless the changed cell is in Column F. It further stops action unless the remainder of the equation (row number minus 39)/34 is zero. If these conditions are met, the above macro calls the second macro. The second macro goes into a regular module. The second macro simply inserts 2 rows below the action or Target cell row and places the active cell in Column A of the second of these two rows. You could move the code in the second macro into the first macro and then have only one macro. You would have to make some changes to the code of the second macro if you did this. I chose to use the 2 macro construct only because you might have some other things that you wanted to add to the second macro. You might have to change some of the numbers in these macros to get exactly what you want. I'll send you a small file with these macros in their proper places if you send me a valid email address for you. My email address is . Remove the "nop" from this address. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target = "" Then Exit Sub If Target.Column < 6 Then Exit Sub If (Target.Row - 39) Mod 34 = 0 Then _ Call DoIt(Target) End Sub Sub DoIt(i As Range) i.Offset(1).Resize(2).EntireRow.Insert Cells(i.Offset(2).Row, 1).Select End Sub "pfa" wrote in message ... Hi, I am a beginning user with very limited knowledge of macros. I am looking to add the following functionality. Presently I have a spreadsheet with enough room for 32 data input rows before I sum two columns' results (columns D&F in rows 40 and 41). Obviously, as time goes by, more rows will be needed for additional input. I would like for the user to, upon pressing the enter key at the last cell of data input for that last data row entered, to have excel automatically insert a new row immediately below and move the cursor to column A for new input. Is this possible without adding too much complexity? Thanks for all the assistance!!! Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simultaneouly insert of rows or col. - Ajit | Excel Discussion (Misc queries) | |||
insert a JPEG into EXCEL 2002 | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |