Copy multiple cells (not in range) from one sheet to another sheet

Question

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.

StartupSpreadsheets · Answer

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.

jcbermu · Answer

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.

Copy multiple cells (not in range) from one sheet to another sheet

2 Answers

Add your own answers!

Ask a Question