Adding Rows to Master Sheet

[Excel Newbie]

I have a master list that is linked to 5 other Team
worksheets.
Team 1: Rows 1 - 50
Team 2: Rows 51 - 100
Team 3: Rows 101 - 150
Team 4: Rows 151 - 200
Team 5: Rows 201 - 250

Now some of the teams need more record space and I need a
Macro that inserts rows to the end of each team row
allocation as follows:

Check if there are less than 10 empty rows between Teams.
Yes: Add rows to maintain 20 free rows between Teams
No: Do not do anyhting.

My current problem is that my macro adds the rows at the
specific row where the macro was created.

Let us say I created the Macro when Row 35 was empty. Now
whenever I run my current Macro, it Inserts the new rows
into 35 even if I have data in row 34, 35, 36, 37.

I hope this makes sense.

Thanks guys!

 

[Bernie Deitrick]

Hey Newbie,

Try the macro below, based on empty cells in column A.

HTH,
Bernie
MS Excel MVP

Sub Macro4Newbie()
Dim myArea As Range
For Each myArea In Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas
If myArea.Cells.Count < 10 Then
myArea(1).Resize(20 - myArea.Cells.Count, 1).EntireRow.Insert
End If
Next myArea
End Sub