CODEWITHSUNDEEP
vbaexcelmacros
Mark Romano
Mark Romano

Reputation: 711

VBA to paste value in next empty cell

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.

Upvotes: 2

Views: 24766

Answers (2)

John Coleman
John Coleman

Reputation: 51998

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

Upvotes: 3

jellz77
jellz77

Reputation: 354

Sub copy_values()
    Dim a As Worksheet
    Dim b As Worksheet

    Set a = Sheets("Worksheet2")
    Set b = Sheets("Worksheet3")

    b.Range("a65536").End(xlUp).Offset(1, 0).Value = a.Range("a1").Value

End Sub

Upvotes: 0

Related Questions