Thread: Updating 4 macros once 1 macro is run

Hi All

I have designed a spreadsheet for work and 1 formula is stumping me completely. My sheet has 5 different groups on with 1 blue row between each to highlight each group.

Group 1 cells A1-A5
Group 2 cells A7-A11
Group 3 cells A13-A17
etc

Each group has formulas as well to work out future dates, change colours on overdue dates and black out boxes depending on which numbers are entered.

Once this document is completed it could have information added on a daily or even hourly basis by up to 10 people so to help out those at work with no computer skills, I added macros for new lines.

Macro 1, once clicked, will insert a new line into A6.
Macro 2, once clicked, will insert a new line into A12.
Both macros will then copy all the formulas down from the line above so everything works.
There is 1 macro for each group so 5 in total.

The problem I am having is that when you run any of them, all the macro's stay the same.
So I run Macro 1. A6 get a new updated line. Meaning the old A6 moves into A7, A7 to A8 etc so when Macro 2 is clicked it then inserts a line into the new A12. This of course means it is not at the bottom of the group.

Can anyone tell me if, and how, I can run the top macro and make it add 1 to the required row for all the macro below it including itself?
This would mean Macro 1 puts a line in A6 and then changes itself so next time it does A7 and also changes Macro 2 from A12 to A13.
Is this even possible or am I stretching too far?

Would help greatly as there are 74 sheets in this workbook and I have to make 2 workbooks.

Let me know if it needs explaining differently.

Thanks all

It sounds to me that while each of the macros is working on different rows they're all doing the same thing - finding the 1st, 2nd, 3rd, 4th or 5th blank line and inserting a line before it and copying down the formula from the previous row.

Therefore one main macro can do all of the work and you just need 5 small macros to call the main one with a parameter specifying which block you're talking about, as below:

Sub InsertAndCopy(lBlockNumber As Long)

Dim lBlockCount As Long
Dim rngCurrentRow As Range

Set rngCurrentRow = ActiveSheet.Range("A1")

For lBlockCount = 1 To lBlockNumber
  Set rngCurrentRow = rngCurrentRow.End(xlDown).Offset(2, 0)
Next lBlockCount

rngCurrentRow.EntireRow.Insert shift:=xlDown

Set rngCurrentRow = rngCurrentRow.Offset(-2, 0)

While rngCurrentRow.Column <= ActiveSheet.Cells(rngCurrentRow.Offset(-1, 0).Row, ActiveSheet.Columns.Count).End(xlToLeft).Column
  rngCurrentRow.Formula = rngCurrentRow.Offset(-1, 0).Formula
  Set rngCurrentRow = rngCurrentRow.Offset(0, 1)
Wend

End Sub

Sub TestMacro()

InsertAndCopy 3

End Sub

Because this code doesn't work on fixed rows so long as you always have your rows separated by a single blank row it will work, irrespective of the size of the individual blocks.