How to consolidate Sheet1 and Sheet2 in Sheet3?

Question

I need to consolidate sheet1 (2 columns: A=REFERENCE and B=QTY) and sheet2 (2 columns: B=REFERENCE and C=QTY) with the result in sheet3, like: A=REFERENCE of sheet1 with sheet2 (unique data) and B=SUM of QTY sheet1 and C=SUM of QTY sheet2.

Sheet1:

Sheet2:

Sheet3: Result and differences only in columns: A, B and C the others have procv.

I used the 'Record Macro' and the workbook if in another PC doesn't work.

Sub consolidar()
'
' consolidar Macro
'

'
    Selection.Consolidate Sources:=Array( _
        "'C:\Users\epontes\Desktop\[APR GR e PICAGEM RENAULT VBA.xlsm]02 GR RENAULT (Consolidar)'!R1C1:R1000C2" _
        , _
        "'C:\Users\epontes\Desktop\[APR GR e PICAGEM RENAULT VBA.xlsm]03 CONTAGEM JAP'!R2C2:R1401C3" _
        ), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    Range("A3").Select
End Sub

The code will function if the data in the workbook is in the exact same format when opened. If it doesn't work on another PC, it seems to me that it's to do with the filepath / permissions referenced in your code. Try replacing the filepath with an object reference to your workbook. — RazorKillBen

RazorKillBen RazorKillBen · Accepted Answer · 2020-02-26T13:31:41

Your code references the filepath of the Worksheet object and thus will only run if executed when the workbook is stored in the exact same location. Therefore sending this to someone else or opening on another PC will cause it to fail. In this case, the filepath is largely redundant and you can simply omit them from the Sheet reference like so:

Sub consolidar()
    Selection.Consolidate Sources:=Array( _
        "'02 GR RENAULT (Consolidar)'!R1C1:R1000C2", _
        "'03 CONTAGEM JAP'!R2C2:R1401C3"), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    Range("A3").Select
End Sub

How to consolidate Sheet1 and Sheet2 in Sheet3?

1 Answers