Auto Insert of Rows

[Gar3th]

Hi

I have a fairly sophisticated requirement which will probably require macros
in Excel 2000. I wish I could have attached an example as it would have
greatly simplified explaining the requirement.

The sheet I have is a list of staff, the cost centres they have authority
for and sign off levels. Basically in column A is a fore name, B is surname,
D cost centres, K is their line manager, L to Z are the names of the items
they can sign off for. The items they can sign off for are marked either
with 'Y' or a monetary value. Other columns are curently blank.

What I need to do is work down the list in column A and B which list staff
by name. For each Cost Centre in column D against that staff member I need
to insert a number of rows which correspond to positive entries in columns L
to Z (minus 1). Each member of staff could have a different number of rows
required. Ideal the entry in A, B, D and K should fill down into the
inserted but empty rows.

Once this is done where there is a positive entry in column L to Z (either Y
or a value greater than 0)I want the header of the column to appear in column
AA starting at the first row containing that staff members name.

In column AB I want the positive entry returned for that column header.
This should also return 'Y' where appropriate.

I would be extremely grateful if someone can automate this process.

Thanks

Gareth

 

[Charles Harmon]

Hi,
If want, you can send me a copy of your file and I'll help you.

Charles
(e-mail address removed)

 

[Sven Svenson]

Thanks to Charles' generous offer this is probably not needed, but since
I already wrote it, here goes.

Since inserting multiple rows can be a bit of a headache, I decided to
simply copy the relevant data onto another spreadsheet. Maybe this will
suffice, maybe not.

I assumed that the original data was on "sheet1", starting on row 2
(leaving row 1 for titles).

Sub Do_It()
Dim i As Long, j As Long
Dim k As Long ' counter tracking number of new entries
Dim priname As String, surname As String
dim cost_centre As String, LineManager As String
'assume that original entries are on sheet1, and they
'are being copied to sheet2
i = 2
k = 0
Do
If Sheets("sheet1").Cells(i, 1) = "" Then
'reached the end of the list
Exit Do
End If

priname = Sheets("sheet1").Cells(i, 1)
surname = Sheets("sheet1").Cells(i, 2)
cost_centre = Sheets("sheet1").Cells(i, 4)
LineManager = Sheets("sheet1").Cells(i, 11)

For j = 12 To 26
If Sheets("sheet1").Cells(i, j) <> "" Then
k = k + 1 'increment new row counter
Sheets("sheet2").Cells(k, 1) = priname
Sheets("sheet2").Cells(k, 2) = surname
Sheets("sheet2").Cells(k, 4) = cost_centre
Sheets("sheet2").Cells(k, 11) = LineManager
Sheets("sheet2").Cells(k, 27) =
Sheets("sheet1").Cells(1, j)
Sheets("sheet2").Cells(k, 28) =
Sheets("sheet1").Cells(i, j)
End If
Next j

i = i + 1
Loop
End Sub




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!