2

The List:- “Problem Sheet”(worksheet).

I have an excel sheet which represents a list of problem issues (Rows) which varies in size every day (eg more or fewer rows); Each row has been assigned a name; The assigned name is always in the same column “M” of the “Problem Sheet”; An individual assignment name does not necessarily appear every day, or it may occur several times (on more than one row) on a given day;

I already have a macro that creates a Unique List (worksheet) of assignment names where each name appearing in column M of the Problem Sheet is recorded once in the “Unique List” worksheet; The same macro creates a single new worksheet (in the same workbook) for every unique occurrence of an Assignment Name. The Assignment Name is recorded automatically in the new individual worksheet tab.

Required:- A macro that will check Column M of the main “Problem Sheet”; For every row / problem where a particular assignment name occurs in Column M of the Problem Sheet, Match the assignment name with the worksheet of the same name, then Copy and Paste the details of the entire row from the “Problem Sheet” to the first blank row of the correct (same assigned name) worksheet in the existing workbook. This routine must be repeated for every row in the Problem Sheet.

Improve this question
3
  • Is there a special reason that forces you to use Excel? Things like this are easily accomplished in SQL, and a database is where I suspect the data comes from anyway. Maybe this can be solved right in the DB? Commented Mar 26, 2009 at 13:25
  • The data is first subjected to alteration and then to quite a bit of formatting, prior to the mail shot, I have been advised that the formatting stage would be problematic using SQL, I already know that these steps can be done in VBA because I have achieved this, but I am now stuck at the mailshot Commented Mar 26, 2009 at 14:56
  • No doubt this is possible in VBA. But it will be a pain in the ass to work out. Normally you would SELECT the right stuff in SQL, and then put that in Excel and format it/whatever. Not put it in Excel first and then try to imitate an SQL query in VBA... Commented Mar 26, 2009 at 18:10

1 Answer 1

Reset to default
0

If order doesn't matter, this might be your best bet

Sub x()
    Dim rngProbs As Range
    With ThisWorkbook.Worksheets("Problem Sheet")
        Set rngProbs = .Range("M1", .Range("M1").End(xlDown))
    End With

    Dim r As Range


    For Each r In rngProbs
        r.EntireRow.Copy
        ThisWorkbook.Worksheets(r.Text).Rows(1).EntireRow.Insert
    Next r

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Answer works perfectly, changed Rows(1) to Rows(2) to avoid title bar already in place in each sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.