VBA to paste value in next empty cell

Question

I have a VBA macro that simply copies the value from one cell (Worksheet1.a1) into another cell (Worksheet2.a1), that is triggered by a button with the assigned macro.

Sub copy_values()

    Worksheets("Worksheet2").Range("a1").Value = Worksheets("Worksheet1").Range("a1").Value

End Sub

The issue is that on Worksheet1, I have a combo-box that lists all available patients. Depending on what patient is selected, the value in Worksheet1.A1 changes.

So say I select "Patient A" and use the button to copy-paste the value of Worksheet1.A1 (lets say the value is "500") into Worksheet2.A2. If I then use the combo-box to select "Patient B", which changes the value of Worksheet1.A1 to "600", and proceed to use the button to paste this value into Worksheet2.A2, the "500" that was previously there is obviously overwrote with the new value.

I need to preserve BOTH values. So, after I paste Patient A's value into Worksheet2.A1, I need Patient B's value to be pasted into Worksheet2.A2 in a dynamic manner. I can't really hardcode this because I have a list of 300+ patients.

Solution

You could do something like this:

Sub copy_values()
    Dim R As Range
    Set R = Worksheets("Worksheet2").Cells(Rows.Count, 1).End(xlUp) 'last cell in Column A with data
    If Len(R.Value) > 0 Then Set R = R.Offset(1)
    R.Value = Worksheets("Worksheet1").Range("a1").Value
End Sub

The idea is to look for the last value in Column A that contains data. The End method will return A1 if column A is blank. The above code checks for that possibility.

Here is a tweaked version which takes an optional source-cell string argument:

Sub copy_values(Optional Source As String = "A1")
    Dim R As Range
    Dim col As Long
    col = Range(Source).Column

    Set R = Worksheets("Worksheet2").Cells(Rows.Count, col).End(xlUp) 'last cell in Column col with data
    If Len(R.Value) > 0 Then Set R = R.Offset(1)
    R.Value = Worksheets("Worksheet1").Range(Source).Value
End Sub

Works like:

Sub test()
    copy_values 'works as before

    copy_values "B1" 'copies value in B1 in first sheet to first available slot in column B in second sheet
End Sub

Comments

[findwindow]

Use an incrementing variable.

[Mark Romano]

Excellent- works perfect. Now if I wanted to extend it to Worksheet1.B1 and Worksheet2.B1, is there a way to optimize the code? What I did was basically copy and paste the block you wrote, then tweaked each line to reference the proper cell. I feel like that may be overkill.

[John Coleman]

You could make the range ("A1", "B1" etc.) a parameter of the sub -- but then the calling code would need to have some way of passing the correct range to the sub.