Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 23rd 04, 04:42 PM
Excel Newbie
 
Posts: n/a
Default 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!

  #2   Report Post  
Old December 23rd 04, 10:56 PM
Bernie Deitrick
 
Posts: n/a
Default

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!





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I have more than 64000 rows in one sheet? blafblaf Excel Discussion (Misc queries) 3 January 17th 05 09:07 AM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 10:05 PM
Master Sheet Row Insertion Need Help Excel Discussion (Misc queries) 1 December 23rd 04 05:10 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 07:43 AM
Why can't I show or hide rows in an outline on a protected sheet? rwlass Excel Discussion (Misc queries) 1 December 9th 04 04:38 PM


All times are GMT +1. The time now is 12:43 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017