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

Adding of time in Excel gives a strange answer

3  Asked on February 6, 2021 by humanitiesclinic


can i change the polling frequency of a running ntpd?

2  Asked on February 6, 2021 by hymie


I think I might have bricked my USB using Rufus

0  Asked on February 6, 2021 by phr33z


I can’t reinstall the program after I remove it

0  Asked on February 5, 2021 by -2


eth0 not set up after is reached

0  Asked on February 5, 2021 by choumat


Use clipboard through WSL?

1  Asked on February 4, 2021 by guitarmony


Set OneDrive Files to Available Online Only in Win7

2  Asked on February 4, 2021 by chiliyago


Batch merge/mux MP4 and SRT files to MKV

2  Asked on February 3, 2021 by xnaas


LED of the F4 key is always on

0  Asked on February 3, 2021 by klaus


Ask a Question

Get help from others!

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