Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows in Multiple worksheet
Recently Im working on creating an Excel worksheet which users need to
insert row(s) under each record (row) in order to enter further information. The number of row(s) inserted equals to the round up value of, for example, column C (i.e. 2.1 becomes 3, 2.6 becomes 3, etc). Column C contains formulate calculates based on the value from column A and B in the same row. There is another worksheet in the same workbook extracts part of the info from the first sheet. Each row in this 2nd sheet associates with the row in the first sheet (each record from both sheets has and keeps the same row number). Im trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows in Multiple worksheet
Create a command button from the Control Toolbox and paste this code in the
button code window by right clicking the button and selecting View Code from the drop down menu. You will need to edit the code to ensure sheet names and sheet index numbers are correct. I suggest you create a copy of your official workbook to test this on. Sub CommandButton1_Click() lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set varRng = ActiveSheet.Range("C2:C" & lastRow) For i = lastRow To 2 Step -1 x = Cells(i, 3).Value If x 0 Then With ActiveSheet .Range(.Cells(i, 3).Offset(1, 0), .Cells(i, 3).Offset(x, 0)).EntireRow.Insert End With With Sheets(2) '<<<Change to suit .Range(.Cells(i, 3).Offset(1, 0), .Cells(i, 3).Offset(x, 0)).EntireRow.Insert End With End If Next End Sub "will" wrote: Recently Im working on creating an Excel worksheet which users need to insert row(s) under each record (row) in order to enter further information. The number of row(s) inserted equals to the round up value of, for example, column C (i.e. 2.1 becomes 3, 2.6 becomes 3, etc). Column C contains formulate calculates based on the value from column A and B in the same row. There is another worksheet in the same workbook extracts part of the info from the first sheet. Each row in this 2nd sheet associates with the row in the first sheet (each record from both sheets has and keeps the same row number). Im trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows in Multiple worksheet
On May 26, 11:57 am, will wrote:
I’m trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! HI, see if this helps: Private Sub CommandButton1_Click() Dim iRow1 As Integer, _ iRow2 As Integer, _ sRows As String iRow1 = 14 iRow2 = 17 sRows = iRow1 & ":" & iRow2 Sheets(Array("Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate Rows(sRows).Select Selection.Insert Shift:=xlDown Sheets("Sheet3").Select Range(Cells(iRow1, 1), Cells(iRow1, 1)).Select End Sub Saludos Jaime Vasquez |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows in Multiple worksheet
thx for your response. My challengers a
1. add row(s) at the right place with the right amount. Row(s) are added right beneath the active cell. The amount of row equals the round up value (i.e. 2.1 becomes 3; 2.6 becomes 3) of the active cell. 2. both worksheet have row(s) added at same place with the same amount I tried your code in my spreadsheet but cannot get the right result. "JLGWhiz" wrote: Create a command button from the Control Toolbox and paste this code in the button code window by right clicking the button and selecting View Code from the drop down menu. You will need to edit the code to ensure sheet names and sheet index numbers are correct. I suggest you create a copy of your official workbook to test this on. Sub CommandButton1_Click() lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set varRng = ActiveSheet.Range("C2:C" & lastRow) For i = lastRow To 2 Step -1 x = Cells(i, 3).Value If x 0 Then With ActiveSheet .Range(.Cells(i, 3).Offset(1, 0), .Cells(i, 3).Offset(x, 0)).EntireRow.Insert End With With Sheets(2) '<<<Change to suit .Range(.Cells(i, 3).Offset(1, 0), .Cells(i, 3).Offset(x, 0)).EntireRow.Insert End With End If Next End Sub "will" wrote: Recently Im working on creating an Excel worksheet which users need to insert row(s) under each record (row) in order to enter further information. The number of row(s) inserted equals to the round up value of, for example, column C (i.e. 2.1 becomes 3, 2.6 becomes 3, etc). Column C contains formulate calculates based on the value from column A and B in the same row. There is another worksheet in the same workbook extracts part of the info from the first sheet. Each row in this 2nd sheet associates with the row in the first sheet (each record from both sheets has and keeps the same row number). Im trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows in Multiple worksheet
thx for your response. Maybe I didn't make myself clear. My challengers a
1. add row(s) at the right place with the right amount. Row(s) are added right beneath the active cell. The amount of row equals the round up value (i.e. 2.1 becomes 3; 2.6 becomes 3) of the active cell. 2. both worksheet have row(s) added at same place with the same amount "JaimeVasquez" wrote: On May 26, 11:57 am, will wrote: Im trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! HI, see if this helps: Private Sub CommandButton1_Click() Dim iRow1 As Integer, _ iRow2 As Integer, _ sRows As String iRow1 = 14 iRow2 = 17 sRows = iRow1 & ":" & iRow2 Sheets(Array("Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate Rows(sRows).Select Selection.Insert Shift:=xlDown Sheets("Sheet3").Select Range(Cells(iRow1, 1), Cells(iRow1, 1)).Select End Sub Saludos Jaime Vasquez |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert rows in Multiple worksheet
thx Jaime, I figured it out.
"JaimeVasquez" wrote: On May 26, 11:57 am, will wrote: Im trying to figure out an easy way for user to do this, like push a button, to insert the required amount of row(s) in both sheets at the right place but still not sure if this is feasible. Hope someone here can offer some comments, thanks! HI, see if this helps: Private Sub CommandButton1_Click() Dim iRow1 As Integer, _ iRow2 As Integer, _ sRows As String iRow1 = 14 iRow2 = 17 sRows = iRow1 & ":" & iRow2 Sheets(Array("Sheet2", "Sheet3")).Select Sheets("Sheet3").Activate Rows(sRows).Select Selection.Insert Shift:=xlDown Sheets("Sheet3").Select Range(Cells(iRow1, 1), Cells(iRow1, 1)).Select End Sub Saludos Jaime Vasquez |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
how do insert multiple rows in between multiple lines | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Insert Multiple Rows | Excel Discussion (Misc queries) | |||
programmatically insert multiple blank rows in worksheet | Excel Programming |