I've searched all over, and I'm unable to find anything regarding copying multiple cells from different rows & columns to a single row in another sheet.

Eg, Copy Cells, A10, F2, F3 & F34 from Sheet 1, to A2 in Sheet 2 and start a new row.

The purpose is to copy certain information from an Invoice (Sheet 1)

  • A10 - Customer
  • F2 - Date
  • F3 - Invoice No
  • F34 - Total Cost

to a single row in another sheet for reporting (Sheet 2)

When I complete a new invoice, I'm hoping to run the same macro to copy these cells onto the next empty row in Sheet 2.

  • Welcome to Super User. This site is for specific questions and is not a script writing service. You will need to share with us what you have attempted and where exactly are you stuck so we can try to help you from there. Have you considered referencing those cells in a hidden row (for instance Z40:Z44), then copying that row of data to the other sheet? – CharlieRB Aug 31 '15 at 11:38

This VBA Macro will do it:

Public Sub resume_invoice()
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(1)
    Set wks1 = wkb.Sheets(2)
    use_row = wks1.Cells(wks1.Rows.Count, "A").End(xlUp).Row
    use_row = use_row + 1
    If wks.Cells(3, 6) <> wks1.Cells(use_row - 1, 3) Then
        wks1.Cells(use_row, 1) = wks.Cells(10, 1)
        wks1.Cells(use_row, 2) = wks.Cells(2, 6)
        wks1.Cells(use_row, 3) = wks.Cells(3, 6)
        wks1.Cells(use_row, 4) = wks.Cells(34, 6)
        Set wkb = Nothing
        Set wks = Nothing
        Set wks1 = Nothing
    End If
End Sub

Go to macro / VBA, under ThisWorkbook insert a module and paste the code.

To make it easy to use you can assign a button to execute it.


To keep it simple and for easier debugging, use helper cells.

In an unused row, row 99 for example, link to your four source cells (A99=A10, B99=F2, C99=F3, D99=F34). Then the macro is straightforward. Hide row 99 to keep it clean.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.