Pasting in the next empty cell in a row using excel vba

Question

I am writing a macro that copies a value from one excel worksheet, and pastes it into another. As seen below, I have a code that correctly copies and pastes my value into the correct worksheet, but I want it to paste into the next empty cell in row 3, instead of just cell "C3". All help is appreciated.

Private Sub CommandButton1_Click()
 Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Dim vMax As Variant

Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

Set wbDATA = Workbooks.Open("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
With wbDATA.Sheets("Contract Task Summary(1)")
    LastRow = .Range("C" & .Rows.Count).End(xlUp).Row

    If LastRow > 1 Then
        .Range("C" & LastRow).Copy
        wsMaster.Range("C" & 3).PasteSpecial xlPasteValues
        wsMaster.Range("C" & 3).PasteSpecial xlPasteFormats
    End If
End With

wbDATA.Close False
End Sub

Answer 1

This is the code you are looking for:

Private Sub CommandButton1_Click()
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Dim vMax As Variant
Dim columnToPaste As Integer
Dim lastColumnToPaste As Integer
Dim lastColumn as Integer

Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

Set wbDATA = Workbooks.Open("C:\Documents and Settings\Michael Palkovitz\My Documents\Test\Contracts Metrics.xlsx")
With wbDATA.Sheets("Contract Task Summary(1)")
    LastRow = .Range("C" & .Rows.Count).End(xlUp).Row

    If LastRow > 1 Then
        .Range("C" & LastRow).Copy
        lastColumn = 3
        lastColumnToPaste = lastColumn + 20
        columnToPaste = lastColumn - 1
        Do
           columnToPaste = columnToPaste + 1
           If IsEmpty(wsMaster.Cells(lastRow, columnToPaste)) Then
               wsMaster.Cells(lastRow, columnToPaste).PasteSpecial xlPasteValues
               wsMaster.Cells(lastRow, columnToPaste).PasteSpecial xlPasteFormats
               Exit Do
           End If
        Loop While (columnToPaste < lastColumnToPaste)
    End If
End With

wbDATA.Close False
End Sub

This is just a basic approach to how the problem should be solved. You should update some values dynamically (e.g., maximum row to check, given by the variable lastRowToPaste).

Note that writing/pasting between two different workbooks is very inefficient. In case of having to repeat this process for a long enough time, I would: open the input spreadsheet and store all the values in a temporary location (depending upon the size, in an array or in a temporary file), close it; open the destination spreadsheet and write the data from this location (without relying on copy/paste). This is a much faster approach to the problem.