More than just copying and pasting

[gtg689a]

Ok, I have tried to explain what I am trying to do with no success. I am going to post my excel document instead, I believe it will be be easier. If you open this workbook, on the manager input sheet, you will see a button that say something about running the macro. Click it and it will take you to the performance grid sheet and put all of the names on the grid where they are supposed to be.

Now:
As you will see there is a table at the bottome that is not filled out. What I want the macro to do is put all of the people in each colored zone, into the appropriate column in the table below the grid. PLEASE give me some hints here, I have tried multiple things, and they do not work. Of couse I could simply do a copy and paste of each cell, but that would look bad.

The end result will be a performance grid that is locked for editing, but with a distribution table below it that is editable.

Thanks in advance!!!!!

[DKenny]

Here's a re-worked version. I made the following changes.

1/ Added some lookup tables in a new sheet.
2/ Added 3 formula colums to the "Manager Input" sheet.
3/ Used Named Ranges for lookup tables and for the 'colour' reports.
4/ Rewrote the code as follows.

Let me know what you think.
I will give more detail on how the changes work tomorrow, need sleep now....

Option Explicit
 
Sub DKBuildReport()
 
Dim rngName As Range
Dim sName As String
Dim lRowNum As Long
Dim lColNum As Long
Dim sColour As String
Dim rngPerfGrid As Range
Dim rngGridCell As Range
Dim rngColour As Range
    
    '-----------------------------------------------
    'Set Initial references and Clear Reports
    '-----------------------------------------------
 
    'Set the reference to the Result Grid
    Set rngPerfGrid = ThisWorkbook.Worksheets("Performance Grid").Range("B7:K11")
    
    'Reset the Performance Grid
    With rngPerfGrid
        .ClearContents
        .Rows.AutoFit
    End With
    
    'Reset the Colour Reports
    With ThisWorkbook.Worksheets("Performance Grid")
        .Range(.Range("rngPINK").Offset(1, 0), .Range("rngPINK").End(xlDown)).ClearContents
        .Range(.Range("rngYELLOW").Offset(1, 0), .Range("rngYELLOW").End(xlDown)).ClearContents
        .Range(.Range("rngORANGE").Offset(1, 0), .Range("rngORANGE").End(xlDown)).ClearContents
        .Range(.Range("rngGREEN").Offset(1, 0), .Range("rngGREEN").End(xlDown)).ClearContents
        .Range(.Range("rngDARKORANGE").Offset(1, 0), .Range("rngDARKORANGE").End(xlDown)).ClearContents
        .Range(.Range("rngPALEBLUE").Offset(1, 0), .Range("rngPALEBLUE").End(xlDown)).ClearContents
        .Range(.Range("rngDEEPBLUE").Offset(1, 0), .Range("rngDEEPBLUE").End(xlDown)).ClearContents
 
    End With
    
    'Start with the first name
    Set rngName = ThisWorkbook.Worksheets("Manager Input").Range("A3")
    
    Do
        
        '-----------------------------------------------
        'Get inputs
        '-----------------------------------------------
        
        'Get the employee name
        sName = rngName.Value
        'The Grid Row
        lRowNum = rngName.Offset(0, 16)
        'The Grid Column
        lColNum = rngName.Offset(0, 17)
        'And the Colour
        sColour = rngName.Offset(0, 18)
        
        
        '-----------------------------------------------
        'Populate Main Grid
        '-----------------------------------------------
        
        'Set the target cell range in the main grid
        Set rngGridCell = rngPerfGrid.Cells(lRowNum, lColNum)
        
        With rngGridCell
            'Either add to the target cell or
            'The next cell to the right
            If .RowHeight < 400 Then
                .Value = .Value & sName & Chr(10)
            Else
                .Offset(0, 1).Value = .Offset(0, 1).Value & sName & Chr(10)
            End If
            
            'If lRowNum = 2 Then Stop
            
            'Adjust the row height
            .Rows.AutoFit
            
            If .RowHeight <= 60 Then
                .RowHeight = 60
            ElseIf .RowHeight < 400 Then
                .RowHeight = .RowHeight + 2
            End If
        End With
        
        
        '-----------------------------------------------
        'Populate Colour Grid
        '-----------------------------------------------
        
        'Set a reference to the colour Header
        Set rngColour = ThisWorkbook.Worksheets("Performance Grid").Range(sColour)
        
        With rngColour
            If .Offset(1, 0).Value = "" Then
                .Offset(1, 0).Value = sName
            Else
                .End(xlDown).Offset(1, 0).Value = sName
            End If
        End With
        
        
        '-----------------------------------------------
        'Move to the next cell
        '-----------------------------------------------
        
        Set rngName = rngName.Offset(1, 0)
    
    Loop Until rngName.Value = ""
End Sub

[gtg689a]

first of all, that is amazing. you really need to set up a tip jar.

I only have one question:
Where are the "rngPALEBLUE", "rngORANGE", etc defined? when I changed them to things like "rngTopPerformer" (I also changed the controls sheet to reflect the change), it started bombing out.

This really is incredible. I could just leave it as the colors, but I want to understand what it is doing and I don't understand how it knows where the color ranges are that it gets the data from, and then which color range in the rngcolour to put it in. Or for that matter, where rngColour is?

[DKenny]

Where are the "rngPALEBLUE", "rngORANGE", etc defined?

They are define on the worksheet as named ranges.
Insert-->>Name-->>Define will show you a list of all the names that have been created and what ranges they refer to.

Also have a look in the Excel help file for "Define named cell references or ranges".

Named ranges are really useful in Excel, I use them all the time in formulas, validation, code, etc...

when I changed them to things like "rngTopPerformer" (I also changed the controls sheet to reflect the change), it started bombing out.

Changing the label (top cell) of the range has no effect, you would need to define this as a new range. I would leave the range names as are in both the code and the sheet and just change the labels.