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!

Related Questions

B85M-K Motherboard 2x8GB ram DDR3 no monitor signal

2  Asked on December 27, 2021 by hamed-mayahian


How to disable user tracking cookies

1  Asked on December 25, 2021 by ravi-kumar-rana


FTP client not working inside Docker container

2  Asked on December 25, 2021 by iskar


Inkscape: Convert fill pattern to path

1  Asked on December 25, 2021 by user281318


Error at OID for old SNMP Hardware

1  Asked on December 25, 2021 by mfb


Can Windows ACL emulate the sticky bit?

0  Asked on December 25, 2021 by user185953


Ask a Question

Get help from others!

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