insert rows in Multiple worksheet

[will]

Recently I’m 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).

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!

 

[JLGWhiz]

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

 

[JaimeVasquez]

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

 

[will]

thx for your response. My challengers are:
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.

 

[will]

thx for your response. Maybe I didn't make myself clear. My challengers are:
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

 

[will]

thx Jaime, I figured it out.

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