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!

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!

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

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!

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

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