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

 

[Peter_SSs]

The only thing is that I don't want to insert rows on every worksheet using my workbook.

.. which is why I asked earlier

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?

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

You have lost me a bit there.
First, I think from about half-way down that text you got mixed up between column B & column L?
Secondly, cells L5 and L7 confuse me as they only relate to a single worksheet. If cell L3 was anything bigger than 1, as I understand it, you would be wanting to insert a varying amount of rows into multiple sheets meaning fixed values in L5 & L7 would be useless.
Thirdly, I don't know what the red text above means or if it has any relevance to the process?

I'm thinking that you perhaps want one of these two things?

1. Rows put into all 115 sheets. The name of each sheet is obtained from column F and the number of rows for each sheet is obtained from column J
   or
2. Rows put into the first ? of the 115 sheets. The name of each sheet is obtained from the first ? rows of column F and the number of rows for each sheet is obtained from the first ? rows of column J.
   ? is obtained from cell L3

If it is not one of those two things then you will have to try to clarify again.