Copy -> Paste Values Efficiency Question

[BBrandt]

Quick question here:

If I want to copy a cell (say... the input sheet, cell C4) and paste only its value somewhere else (say... the results sheet, cell B4), is there a better way to do it than

[C4].Copy
        Sheets("Results").Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False

?

I was thinking something along the lines of using [C4].Copy(Destination) but having it know to only put the values. Just trying to clean up my code a bit, thanks in advance!

 

[VoG]

This would be more efficient

Sheets("Results").Range("B4").Value = Range("C4").Value

 

[wddnld]

Sheets("Results").Range("B4") = Sheets("Input").Range("C4")

 

[BBrandt]

Cheers, thanks!

Follow up question:
If C4 is part of a table with a variable number of rows depending on other user input, how might I go about performing that operation for every row of the table, starting at C4 and ending at ??4 where ?? is the last row in the table? Thanks again.

 

[VoG]

Try

Sub cpy()
Dim LR As Long
With Sheets("Input")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    Sheets("Results").Range("B4:B" & LR).Value = .Range("C4:C" & LR).Value
End With
End Sub

 

[BBrandt]

Thanks! That worked like a charm and is roughly a half the length of what I came up with. I rather like efficient code.

 

[VoG]

Thanks! That worked like a charm and is roughly a half the length of what I came up with. I rather like efficient code.

You are welcome

If you 'lurk' on this board and study the solutions offered by the more prolific posters you will pick up methods to make your code more efficient.

 

[BBrandt]

Yep, I'm sort of teaching myself VBA, and when I say that, I mean that sites like Mr. Excel are teaching me VBA.

Final question (I swear... for today anyway):

If I want to fill column D on the results page with a number that won't change per row (ex. all cells in the column will be equal to cell F1 from the inputs page), but will also match the variable table height, what would I do differently from the above code?

I know this could be accomplished by filling the column with an IF statement inside excel (ex. =IF(AND(B4="",C4=""),"",'Inputs'!F1), but in the long run it will actually be more convenient if the macro does it for me. Thanks.

 

[BBrandt]

Apparently I can't edit my last post anymore, but regardless, I've figured it out. Took a couple tries but the working code is:

    With Sheets("Results")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        .Range("D4:D" & LR).Value = [num]
    End With

...which fills column D with the constant cell labeled "num" (actually cell D12 on the input page) based on the number of rows in the previously filled column C.

Cheers