0

I have 2 tables:

table1 (3rd party sales) table2 (Change of sales)

I have a summarize table:

table4 (3rd party and change of sales)

I want to consolidate each columns from table1 and table2 into table4.

I use ListObject because the order of the column might change and I do not now in advance the number of line. This is extracted from the system.

I tried using Offset, but I receive #N/A values. 

Sub Macro1()
'
' Macro1 Macro
'
'
Dim Sht1 As Worksheet
Dim Rng1 As Range
Dim tb1 As ListObject
'Definition of the differents tables
    Set Sht1 = Sheets("Sheet1")
    Set Rng1 = Range("Table1")
    Set tb1 = Rng1.ListObject

    Set Sht2 = Sheets("Sheet1")
    Set Rng2 = Range("Table2")
    Set tb2 = Rng2.ListObject

    Set Sht3 = Sheets("Sheet1")
    Set Rng3 = Range("Table4")
    Set tb3 = Rng3.ListObject

    'resizing the table3 to consolidate data from table 1 and table 2
    tb3.Resize tb3.Range.Resize(tb1.ListRows.Count + tb2.ListRows.Count + 1)

'Consolidating the column Period from Table 1 and Table 2 into Table 3
    tb3.ListColumns("Period").DataBodyRange.Value = tb1.ListColumns("Period").DataBodyRange.Value ' works well
    tb3.ListColumns("Period").DataBodyRange.Value = tb2.ListColumns("Period").DataBodyRange.Value ' return N/A value
'Consolidating the column Profit Ctr from Table 1 and Table 2 into Table 3
    tb3.ListColumns("Profit Ctr").DataBodyRange.Value = tb1.ListColumns("Profit Ctr").DataBodyRange.Value
    tb3.ListColumns("Profit Ctr").DataBodyRange.Value = tb2.ListColumns("Profit Ctr").DataBodyRange.Value
'Consolidating the column Partner PC from Table 1 and Table 2 into Table 3
    tb3.ListColumns("Partner PC").DataBodyRange.Value = tb1.ListColumns("Partner PC").DataBodyRange.Value
    tb3.ListColumns("Partner PC").DataBodyRange.Value = tb2.ListColumns("Partner PC").DataBodyRange.Value
End Sub

I expected to have the following:

table1-A table1-B table1-C table2-A table2-B table2-C

I have: table1-A table1-B table1-C N/A N/A N/A

Corentin Canet
  • 35
  • 5
  • It looks like you're overwriting... you have two instances of `tb3.ListColumns(...).DataBodyRange.Value` for each column. Can you explain? – BigBen Oct 18 '19 at 12:25
  • 1
    You might take a look at `Power Query` (available in Excel 2010+) for another approach to this procedure. – Ron Rosenfeld Oct 18 '19 at 12:31
  • That is precisely the point where I am block. Ideally I was thinking to do something like that: `tb3.ListColumns(...).DataBodyRange.Value = _ tb1.ListColumns(...).DataBodyRange.Value tb2.ListColumns(...).DataBodyRange.Value` – Corentin Canet Oct 18 '19 at 12:32
  • If Power Query is an option, use that - it's a much better approach. Otherwise you could read each original column into an array, combine the two arrays and then write to the destination table in one go. – BigBen Oct 18 '19 at 12:33
  • What about using [Consolidate data in multiple worksheets](https://support.office.com/en-us/article/consolidate-data-in-multiple-worksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b?ui=en-US&rs=en-US&ad=US) ? I've used in the past to consolidate different groups of data. You can even link them. The only bad thing is that formulas will be consolidated as values, and you will need to input them again. – Foxfire And Burns And Burns Oct 18 '19 at 12:35
  • The dashboard I am working on has 7 tab for data extraction from our system. What I want is to run 1 macro which will process all the data. I want to limiite manual work, therefore I am trying to find the way how to consolidate 2 columns into 1. the following works fine: `tb3.ListColumns(...).DataBodyRange.Value = tb1.ListColumns(...).DataBodyRange,Value` works well. Is there a easy way to actually do the following: `tb3.ListColumns(...).DataBodyRange.Value = tb1.ListColumns(...).DataBodyRange,Value & tb2.ListColumns(...).DataBodyRange,Value ` works well. – Corentin Canet Oct 18 '19 at 12:46

0 Answers0