Adding Rows to Master Sheet
 

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!

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).Area s
If myArea.Cells.Count < 10 Then
myArea(1).Resize(20 - myArea.Cells.Count, 1).EntireRow.Insert
End If
Next myArea
End Sub

"Excel Newbie" wrote in message
...
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!