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

Super User Asked by Brendan on November 5, 2020

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.

2 Answers

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.

Answered by StartupSpreadsheets on November 5, 2020

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.

Answered by jcbermu on November 5, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP