Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Inserting Rows
Hi, I have never posted before but hope that someone can help me with a
problem now that I have. I am developing a spreadsheet in which I want to give users the power to insert additional rows, which sounds easy enough. However what I want is to do is have a macro they can use to do this, as for every row that is inserted I need the formulas present in the existing rows to be copied into newly inserted row. I don't think the users will have the awareness to copy the formulas into the new rows themselves, hence I want to make it happen automatically. I found some VB script in another posting that has almost solved this for me (below), but the problem is that running this inserts a row in the same row position each time (row 18). What I want is for the user to either highlight the row of their choosing or click into a cell in that row and for the macro to insert a row in the position they have selected. Can anyone suggest what I am doing wrong, please? I am a novice with VB script so don't know where to start. Rows("18:18").Select Selection.Insert Shift:=xlDown Range("H15").Select INSERTPT = ActiveCell.Value Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("H18").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Inserting Rows
Hi,
The following will insert a row from the position they have selected... ActiveCell.EntireRow.Select 'Rows("18:18").Select Selection.Insert Shift:=xlDown ActiveCell.Offset(0, 7).Select 'Range("H15").Select INSERTPT = ActiveCell.Value Range(Selection, Selection.End(xlToRight)).Select Selection.Copy ActiveCell.Offset(3, 0).Select 'Range("H18").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False I have replaced the original code with mine by making them comments (i.e. '). Rob Edwards Always look on the bright side of life! *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Inserting Rows
Here is one I use to copy the row above to a new row at the bottom
Sub newline() With Cells([ChecksA].Rows.count + 7, 1) .Offset(0) = .Offset(-1)'copies above date End With End Sub Can be modified to insert a row. -- Don Guillett SalesAid Software "Dickie Worton" <Dickie wrote in message ... Hi, I have never posted before but hope that someone can help me with a problem now that I have. I am developing a spreadsheet in which I want to give users the power to insert additional rows, which sounds easy enough. However what I want is to do is have a macro they can use to do this, as for every row that is inserted I need the formulas present in the existing rows to be copied into newly inserted row. I don't think the users will have the awareness to copy the formulas into the new rows themselves, hence I want to make it happen automatically. I found some VB script in another posting that has almost solved this for me (below), but the problem is that running this inserts a row in the same row position each time (row 18). What I want is for the user to either highlight the row of their choosing or click into a cell in that row and for the macro to insert a row in the position they have selected. Can anyone suggest what I am doing wrong, please? I am a novice with VB script so don't know where to start. Rows("18:18").Select Selection.Insert Shift:=xlDown Range("H15").Select INSERTPT = ActiveCell.Value Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("H18").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Inserting Rows
Thanks Bob,
Your script does almost what I want, and it's my own fault for not being specific that it isn't quite right. Each row in the spreadsheet has numeric and text values in the first few columns, which I don't want to copy into the inserted row. However, there are formula in subsequent columns of the row that I do wish to copy into the inserted row, along with some conditional formatting. I suspect what I might need is a combination of the script from Rob and that from your own posting, although it could be that what I am after is something that simply can't be done. As before, any suggestions you have would be gratefully received. "Bob Phillips" wrote: ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dickie Worton" <Dickie wrote in message ... Hi, I have never posted before but hope that someone can help me with a problem now that I have. I am developing a spreadsheet in which I want to give users the power to insert additional rows, which sounds easy enough. However what I want is to do is have a macro they can use to do this, as for every row that is inserted I need the formulas present in the existing rows to be copied into newly inserted row. I don't think the users will have the awareness to copy the formulas into the new rows themselves, hence I want to make it happen automatically. I found some VB script in another posting that has almost solved this for me (below), but the problem is that running this inserts a row in the same row position each time (row 18). What I want is for the user to either highlight the row of their choosing or click into a cell in that row and for the macro to insert a row in the position they have selected. Can anyone suggest what I am doing wrong, please? I am a novice with VB script so don't know where to start. Rows("18:18").Select Selection.Insert Shift:=xlDown Range("H15").Select INSERTPT = ActiveCell.Value Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("H18").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Inserting Rows
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1).PasteSpecial xlFormulas -- Regards, Tom Ogilvy "Dickie Worton" wrote: Thanks Bob, Your script does almost what I want, and it's my own fault for not being specific that it isn't quite right. Each row in the spreadsheet has numeric and text values in the first few columns, which I don't want to copy into the inserted row. However, there are formula in subsequent columns of the row that I do wish to copy into the inserted row, along with some conditional formatting. I suspect what I might need is a combination of the script from Rob and that from your own posting, although it could be that what I am after is something that simply can't be done. As before, any suggestions you have would be gratefully received. "Bob Phillips" wrote: ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dickie Worton" <Dickie wrote in message ... Hi, I have never posted before but hope that someone can help me with a problem now that I have. I am developing a spreadsheet in which I want to give users the power to insert additional rows, which sounds easy enough. However what I want is to do is have a macro they can use to do this, as for every row that is inserted I need the formulas present in the existing rows to be copied into newly inserted row. I don't think the users will have the awareness to copy the formulas into the new rows themselves, hence I want to make it happen automatically. I found some VB script in another posting that has almost solved this for me (below), but the problem is that running this inserts a row in the same row position each time (row 18). What I want is for the user to either highlight the row of their choosing or click into a cell in that row and for the macro to insert a row in the position they have selected. Can anyone suggest what I am doing wrong, please? I am a novice with VB script so don't know where to start. Rows("18:18").Select Selection.Insert Shift:=xlDown Range("H15").Select INSERTPT = ActiveCell.Value Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("H18").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Inserting Rows
Thanks to all, with your help I now have this working exactly as I want it.
Dickie "Don Guillett" wrote: Here is one I use to copy the row above to a new row at the bottom Sub newline() With Cells([ChecksA].Rows.count + 7, 1) .Offset(0) = .Offset(-1)'copies above date End With End Sub Can be modified to insert a row. -- Don Guillett SalesAid Software "Dickie Worton" <Dickie wrote in message ... Hi, I have never posted before but hope that someone can help me with a problem now that I have. I am developing a spreadsheet in which I want to give users the power to insert additional rows, which sounds easy enough. However what I want is to do is have a macro they can use to do this, as for every row that is inserted I need the formulas present in the existing rows to be copied into newly inserted row. I don't think the users will have the awareness to copy the formulas into the new rows themselves, hence I want to make it happen automatically. I found some VB script in another posting that has almost solved this for me (below), but the problem is that running this inserts a row in the same row position each time (row 18). What I want is for the user to either highlight the row of their choosing or click into a cell in that row and for the macro to insert a row in the position they have selected. Can anyone suggest what I am doing wrong, please? I am a novice with VB script so don't know where to start. Rows("18:18").Select Selection.Insert Shift:=xlDown Range("H15").Select INSERTPT = ActiveCell.Value Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Range("H18").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting rows via macro | Excel Programming | |||
Inserting Rows Macro -- please help!! | Excel Programming | |||
Inserting rows macro | Excel Programming | |||
Inserting Rows Through a Macro | Excel Programming | |||
INSERTING ROWS WITH A MACRO | Excel Programming |