VBA Compile Error for each control variable must be variant or object

jazzyelsie 

Point 1: « When I run the code, nothing outputs [i.e., nothing is written to the target cells] »

It turns out that's not correct. The code is furiously writing values into the target cells. In your CopyData procedure, the second For Each loop picks up the value from row 10 of the Master column and conditionally starts the third For Each loop, which conditionally writes the value into row 10 of a target column, and then into row 11 of that target column, and then into row 12, and so on, and so on, till every cell of the target column (conditionally) has that row 10 value. Then the second For Each loop does the same from row 11, etc. You're only seeing nothing in the target cells during your tests because the last copyable master cell is empty.

That third For Each loop should not exist. As the target rows will always be the same rows as the master rows, you should replace

                For Each targetCell In targetRange
                    If Not IsGrey(targetCell) Then
                        targetCell.Value = cell.Value
                    End If
                Next targetCell

with

                Set targetCell = ws.Range(Left$(colRange, 1) & cell.Row)
                If Not IsGrey(targetCell) Then
                    targetCell.Value = cell.Value
                End If

You would see the problem if you stepped through the code in debugging mode. Here are some online resources on debugging:

• How to Debug in VBA (you won't use the Start button to begin execution, but breakpoints are very useful)
• Use the Immediate window (VBA) 

Point 2: « there seems to be inconsistencies in how vba is handling [the For Each control variable] »

No, VBA is consistent in requiring that the control variable for a For Each loop (colRange, in this case) be a Variant or an Object. If you needed colRange to be a String (you don't; VBA will happily use a string value inside a Variant), you would instead store the result of the Split function into a defined variable (e.g., Dim TargetRanges as String() ) and use a standard For loop (with a range of LBound(TargetRanges) To UBound(TargetRanges) ).

But as noted under Point 1, that's an inappropriate loop. And by now you probably realize that you did not need to put complete range identifiers into targetCols. (As long as the target rows are the same rows as the master rows, you don't need GetSelectedRanges to specify build range identifiers at all. Only if the rows were different between the two would you need to specify the starting row in that procedure, and more code would be required in CopyData to advance through the target range.)

Point 3: Your code for validating user-input column letters requires that they enter an upper-case letter. It's better design to accept any-case letters, and use the UCase$ function on their input as needed.

Point 4: I strongly recommend that each of the modules into which you place VBA code (standard modules, workbook modules, worksheet modules, UserForm modules, etc.) should start with an Option Explicit statement. That statement forces you to declare any variables you use, which avoids most bugs due to misspelling of variable names.