Insert Custom Rows VBA - Multiple Worksheets

[ststern45]

Hi Everyone,
I have tried numerous attempts to insert a new blank cell range in existing worksheets but getting the incorrect result.

This is the line of code I'm currently trying:
Rows("20:174").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
The result inserts rows from 20 through 329

I also tried using the code below using the custom ranges 20 through 174:
aRng = A
eRng = E
Range G1 = 20
Range G2 = 174
Rows.Range(aRng & Range("G1").Value & ":" & eRng & Range("G2").Value).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

The result using this code also inserts rows from 20 through 329

Is there a reason the total rows inserted are from 20:329 instead of 20:174?

The aRng and eRng are in the worksheet labeled InsertRows.
There are a total of 155 worksheets which have existing data in each. The data begins on row 20 in all 155 worksheets.

Thank you in advance

 

[ststern45]

I did a search and found the following code:

Dim iRow As Long
Dim iCount As Long
Dim i As Long

iCount = InputBox(Prompt:="How many rows you want to add?")
iRow = InputBox _
(Prompt:="After which row you want to add new rows? (Enter the row number")

For i = 1 To iCount
    Rows(iRow).EntireRow.Insert
Next i

I changed the code to eliminate the Input Boxes:
How do I keep the code below from a continuous loop?

  iCount = Range("A2")
   iRow = Range("A3")

For i = 1 To iCount
    Rows(iRow).EntireRow.Insert
Next i

Thank you

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time in post 2.

That code does not do a continuous loop for me. It just inserts the number of rows shown in A2, starting at the row shown in A3.
(Side note: There is no need to use EntireRow since Rows(iRow) already is an entire row. )

However, there is no need to add them one at a time, you can add them all at once like this.
Edit: On re-reading, perhaps this is what you meant by avoiding a loop?

Sub Insert_Rows_v2()
  Dim iRow As Long
  Dim iCount As Long
 
  iCount = Range("A2").Value
  iRow = Range("A3").Value

  Rows(iRow).Resize(iCount).Insert
End Sub

 

[ststern45]

Thank you Peter for the code.

The code is inserting too many rows in the 1 worksheet I want to test to make sure this works.
For example, Cell A2 = 174 and Cell A3 = 20
The total inserted rows after running the code should be a total of 174 rows 20 through 193.
After I run the code the inserted rows are from 20 through 367 which is a total of 348 rows.

 

[ststern45]

Is there a way for the 2 values come from 1 worksheet?
For example, if I have 1 worksheet that has the values for A2 and A3 but are in cells L6 and L7 of worksheet MyInfo.
Instead of have the 2 values in each of the 115 worksheets A2 & A3, use the 2 values from the MyInfo worksheet cells L6 and L7

Add 2 lines of code:
Dim aRng = Long
Dim bRng = Long

iCount = Range(aRng).Value
iRow = Range(bRng).Value

Thank you

 

[Peter_SSs]

The code is inserting too many rows in the 1 worksheet I want to test to make sure this works.
For example, Cell A2 = 174 and Cell A3 = 20
The total inserted rows after running the code should be a total of 174 rows 20 through 193.
After I run the code the inserted rows are from 20 through 367 which is a total of 348 rows.

Code works fine for me, see below.
Sounds like perhaps you have the looping row-by-row insert as well as the one-off multiple-row insert both going?
Or do you have any other code in the workbook, perhaps a Worksheet_Change code that could be inserting more rows?
Where do you have the code? Is it in a standard module?
Have you modified the code in any way or combined it with other code? If so, please post the full code (remembering my first point in post 3)?

The input values could certainly be in another sheet but let's try to solve the number of rows inserted problem first.
There is now mention of 115 worksheets which were not mentioned originally. Do the rows have to be inserted in all of them? If not, how do we know which 115 sheets?

Before:

ststern45_1.xlsm
	A	B	C
1			1
2	174		2
3	20		3
4			4
5			5
6			6
7			7
8			8
9			9
10			10
11			11
12			12
13			13
14			14
15			15
16			16
17			17
18			18
19			19
20			20
21			21
22			22
23			23
24			24
25			25
26
27
Sheet1

After the v2 code above:

ststern45_1.xlsm
	A	B	C
1			1
2	174		2
3	20		3
4			4
5			5
6			6
7			7
8			8
9			9
10			10
11			11
12			12
13			13
14			14
15			15
16			16
17			17
18			18
19			19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194			20
195			21
196			22
197			23
198			24
199			25
200
Sheet1

 

[ststern45]

This is the code I'm trying to use to insert rows in each of the worksheets.
I'm having an issue using XL2BB add-in.
I selected the range from A1 through R15.
Selected MiniSheet and the program just hangs.

The code below does add the correct number of blank rows.
Could you explain how to use the range values in cell L6 and L7? L6 = 174, L7 = 20?
I'm using the Index and Match formula which calculates how many rows based on the numbers 1 through 115.
Thank you!!

Sub InsertLines1()

Dim aRng As String

aRng = Sheets("Pick3").Range("L1").Value  'Column B1 through B115


With ActiveSheet
For Each cll In .Range(aRng & Range("Ll2").Value & ":" & aRng & Range("L3").Value).Cells '


.Range("L4").Value = cll.Value
   
        InsertLines2
       
    Next cll
    End With
   
       
End Sub



Sub InsertLines2()

Dim fRng As String, Sname1 As String
Dim gRng As String
Dim iRow As Long
Dim iCount As Long

Sheets("Pick3").Select

fRng = Sheets("Pick3").Range("L6").Value
gRng = Sheets("Pick3").Range("L7").Value

Sname1 = Sheets("Pick3").Range("L5").Value

Sheets(Sname1).Select
  
 
iCount = Range("A2").Value
iRow = Range("A3").Value

Rows(iRow).Resize(iCount).Insert

         
Sheets("Pick3").Select
Range("N1").Select

       
End Sub

 

[ststern45]

I tried changing the following code below but I'm getting an error:

iCount = Range(fRng).Value
iRow = Range(gRng).Value

Thank you for all your help!!

 

[Peter_SSs]

This is the code I'm trying to use to insert rows in each of the worksheets.

Is that all the code is trying to do?
There is too much unknown in your code to work out what is actually happening but it seems to me you are trying to do something else as well. I'm just not sure if that is the case or if so, what it is you are trying to do. I think you are going to have to simply things to try to get a better start.

I'm having an issue using XL2BB add-in.
I selected the range from A1 through R15.
Selected MiniSheet and the program just hangs.

Is the XL2BB problem with just this worksheet/workbook as you had it working fine here?


Suggestion to test the concept.

1. Start a whole new workbook with 4 worksheets and no vba code (until step 5 below).
2. Name one of the worksheets 'Pick3' (Doesn't matter what the other 3 are called)
3. On Pick 3 enter 10 in L6 and 4 in L7 (So we are going to try to add 10 new rows starting at row 4)
4. On all 4 sheets, including 'Pick3', add some data in each cell of A1:A10
5. Put the code below into a standard module (It should be Module1)
6. With any one of the four worksheets active, run the code.
7. Now look at each of the 4 worksheets. There should be 10 blank rows (4:13) in each worksheet except 'Pick3'
8. Let us know how that went and perhaps we can build from there

Sub Insert_Rows_v3()
  Dim ws As Worksheet
  Dim iRow As Long
  Dim iCount As Long
  
  iCount = Sheets("Pick3").Range("L6").Value
  iRow = Sheets("Pick3").Range("L7").Value
  
  For Each ws In Worksheets
    If ws.Name <> "Pick3" Then
      ws.Rows(iRow).Resize(iCount).Insert
    End If
  Next ws
End Sub

 

[ststern45]

I followed all your steps above.
Ran the code.
Your correct. The 3 worksheets now have 10 new empty rows from row 4 through 13.
OK I see how you added the lines of code using the Worksheet .ws
That is very cool.
The only thing is that I don't want to insert rows on every worksheet using my workbook.
I have a list of workbook names that will be used to insert custom rows. That is where I use the sname code.

This is how I have the Pick3 worksheet setup from the image below.
There are a total of 115 pick 3 games (1 through 115) in cell B1 through B115
Range F1 through F115 contain the corresponding sheet names
Range J1 through J115 contain the row values I want to insert into that particular worksheet.
Cell L1 = B which are the values from B1 through B115 (1 - 115)
Cell B2 = 1 This is the starting point.
Cell B3 = 1 This is the end (Note: this could be any value from 1 to 115. I set B3 to 1 so I could test the code to make sure it works.
Cell B4 = 1 This example it is the active value that I want to test. It is also used to for formulas in cell B5 and B7.
Cell B5 contains the following formula: =INDEX($F$1:$F$115,MATCH($L$4,$B$1:$B$115,0)) This is the name of the worksheet to insert the Rows.
Cell B6 contains the value 20. This is the Row number I want the insertion Row to begin
Cell B7 contains the following formula: =INDEX($J$1:$J$115,MATCH($L$4,$B$1:$B$115,0)) This is the number of Rows I want to insert
Cells B8 and B9 I will be using in another step. This is where I copy data from 1 workbook to the current workbook.
The Toolbar is used to Run the code.

Thank you again for your help Peter