CODEWITHSUNDEEP
excelvbamultiple-columnsconsolidation
Epontes
Epontes

Reputation: 15

How to consolidate Sheet1 and Sheet2 in Sheet3?

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:
enter image description here

Sheet2:
enter image description here

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

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

Upvotes: 0

Views: 146

Answers (1)

RazorKillBen
RazorKillBen

Reputation: 573

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

Upvotes: 1

Related Questions