Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Insert of Rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Insert of Rows
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Insert of Rows
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Insert New Rows | Excel Discussion (Misc queries) | |||
how to auto insert rows in Excel | Excel Discussion (Misc queries) | |||
Auto Insert Rows of Data?? | Excel Discussion (Misc queries) | |||
Auto Insert of Rows | Excel Worksheet Functions | |||
Auto Insert Rows | Excel Programming |