VBA code to copy data from multiple sheets and consolidate into a single sheet by matching headers

clarkkent111

New Member
Joined
Mar 27, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have data in multiple sheets. Each of the sheets has common headers which aren't exactly in the same order. I need VBA code to match headers with all the sheets and paste all the data into a sheet called "Consolidated " by matching the headers.

Thanks in advance!
 

Attachments

  • IMG 1.png
    IMG 1.png
    60.5 KB · Views: 13
  • IMG 2.png
    IMG 2.png
    75 KB · Views: 13
  • IMG 3.png
    IMG 3.png
    63.8 KB · Views: 13

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
pictures don't tell that much.

Can you add the headerrow and some rows of data of such a sheet + the headerrow of the consolidated sheet.
I hope (=correct?) that both have matching names match = copy a column "customer" to a column "customer" etc.
If not then a way of telling what the order is for example "5,3,7,8,9,1,2,..." explains me that col 5 of that sheet is the 1st column of the consolidated sheet, 3 is the 2nd, 7 the 3rd, etc.

Use the XL2BB-tool instead of a picture.
 
Upvote 0
pictures don't tell that much.

Can you add the headerrow and some rows of data of such a sheet + the headerrow of the consolidated sheet.
I hope (=correct?) that both have matching names match = copy a column "customer" to a column "customer" etc.
If not then a way of telling what the order is for example "5,3,7,8,9,1,2,..." explains me that col 5 of that sheet is the 1st column of the consolidated sheet, 3 is the 2nd, 7 the 3rd, etc.

Use the XL2BB-tool instead of a picture.
Let me break it down

In the Sheet below, There are 18 columns with data. This is Sheet 1

New Microsoft Excel Worksheet.xlsx
ABCDEFGHIJKLMNOPQR
1IFSCABINVOICE NOPO#BOXUNIT NOLR NOVEHICLE TYPEVEHICLE NOLR DATEFTY IN TIMEFTY OUT TIMEDATESEAL NODRIVER NAMEMOBLI NORR NumberVENDOR
2990322ABCDE20877563211610121123ABC12327-03-202227-03-202227-03-202227-03-2022U1.32.11Max123456789065432ABS
32087766321172318:3019:30
420877763211855
5208778632119123
620877963212054
720878063212122
82087816321227
92087826321235
10
Sheet 1


In Sheet 2 as shown below, we have the same headers but not in the same order as sheet 1. For instance the header in column Q in Sheet 1 is the same as column H in Sheet 2.

New Microsoft Excel Worksheet.xlsx
ABCDEFGHIJKLMNOPQR
1IFSCABPO#BOXVEHICLE NOUNIT NOLR NORR NumberVEHICLE TYPEINVOICE NOSEAL NOLR DATEFTY IN TIMEFTY OUT TIMEDATEDRIVER NAMEMOBLI NOVENDOR
2990322ABCDE9876510ABC12312112365432567899U1.32.1127-03-202227-03-202227-03-202227-03-2022Max1234567890UYT
3987662356790018:3019:30
49876755567901
598768123567902
69876954567903
79877022567904
8987717567905
9987725567906
10
11
12990365DFGHJK6321161DEF1231045671234545775U2.32.1127-03-202227-03-202227-03-202227-03-2022Max1234567890XYZ
1363211734577618:3019:30
14632118445777
156321195445778
166321201145779
176321212245780
186321226545781
19632123945782
Sheet2



Finally we have the consolidated sheet which would match the headers from sheet 1 and sheet 2 respectively and copy all the data. Basically the table shown below has data from Sheet 1 and Sheet 2 combined by matching the headers.

New Microsoft Excel Worksheet.xlsx
ABCDEFGHIJKLMNOPQR
1IFSCABINVOICE NOPO#BOXUNIT NOLR NOVEHICLE TYPEVEHICLE NOLR DATEFTY IN TIMEFTY OUT TIMEDATESEAL NODRIVER NAMEMOBLI NORR NumberVENDOR
2990322ABCDE20877563211610121123ABC12327-03-202227-03-202227-03-202227-03-2022U1.32.11Max123456789065432ABS
32087766321172318:3019:30
420877763211855
5208778632119123
620877963212054
720878063212122
82087816321227
92087826321235
10
11990322ABCDE5678999876510121123ABC12327-03-202227-03-202227-03-202227-03-2022U1.32.11Max123456789065432UYT
12567900987662318:3019:30
135679019876755
1456790298768123
155679039876954
165679049877022
17567905987717
18567906987725
19
20
21990365DFGHJK457756321161104567DEF12327-03-202227-03-202227-03-202227-03-2022U2.32.11Max123456789012345XYZ
2245776632117318:3019:30
23457776321184
244577863211954
254577963212011
264578063212122
274578163212265
28457826321239
Consolidated
 
Upvote 0
Clarkkent.xlsm
VBA Code:
Sub consolideren()
     For Each sh In Array("sheet1", "sheet2")                   'loop through this sheets
          With Sheets(CStr(sh))                                 'that sheet
               Set c = Range("Header_" & sh)                    'named range for the headerrow
               kol = c.Offset(-1).Value                         'he row above shows the right columnumber to copy
               myrow = .Range("C" & Rows.Count).End(xlUp).Row   'row of the last used C-cell
               If myrow >= 3 Then                               'there is data
                    a = .Range("A2").Resize(myrow - 1, c.Columns.Count).Value2     'copy to array (now still with the header to show the right order, later without the header)
                    .Range("A1").Resize(UBound(a)).Name = "rijen"     'a range with the same number of rows as a, starting at row 1
                    rijen = [row(rijen)]                        'a matrix with incrementing numbers
                    With Sheets("consolidated").Range("C" & Rows.Count).End(xlUp).Offset(2, -2)     'destination
                         .EntireRow.Interior.Color = RGB(0, 255, 0)     'color that new row
                         .Resize(UBound(a), UBound(a, 2)).Value = Application.Index(a, rijen, kol)     'copy your data to consolidated in the right column-order
                    End With
               End If
          End With
     Next
End Sub
 
Upvote 0
Clarkkent.xlsm
VBA Code:
Sub consolideren()
     For Each sh In Array("sheet1", "sheet2")                   'loop through this sheets
          With Sheets(CStr(sh))                                 'that sheet
               Set c = Range("Header_" & sh)                    'named range for the headerrow
               kol = c.Offset(-1).Value                         'he row above shows the right columnumber to copy
               myrow = .Range("C" & Rows.Count).End(xlUp).Row   'row of the last used C-cell
               If myrow >= 3 Then                               'there is data
                    a = .Range("A2").Resize(myrow - 1, c.Columns.Count).Value2     'copy to array (now still with the header to show the right order, later without the header)
                    .Range("A1").Resize(UBound(a)).Name = "rijen"     'a range with the same number of rows as a, starting at row 1
                    rijen = [row(rijen)]                        'a matrix with incrementing numbers
                    With Sheets("consolidated").Range("C" & Rows.Count).End(xlUp).Offset(2, -2)     'destination
                         .EntireRow.Interior.Color = RGB(0, 255, 0)     'color that new row
                         .Resize(UBound(a), UBound(a, 2)).Value = Application.Index(a, rijen, kol)     'copy your data to consolidated in the right column-order
                    End With
               End If
          End With
     Next
End Sub
Getting an error as shown in the image below.
 

Attachments

  • Img 4.png
    Img 4.png
    62.2 KB · Views: 11
Upvote 0
The code suspects that every such sheet has a named range "Header_Sheetx" where sheetx is the name of that worksheet.
You don"t use tables (=listobjects), so that's a way to tell VBA were your data starts, how many columns there are and with the row above, the order to copy to the consolidated sheet.

So make that every such sheet has as structure as in my example, the orange range with formulas and the green range the named range.
 
Upvote 0
The code suspects that every such sheet has a named range "Header_Sheetx" where sheetx is the name of that worksheet.
You don"t use tables (=listobjects), so that's a way to tell VBA were your data starts, how many columns there are and with the row above, the order to copy to the consolidated sheet.

So make that every such sheet has as structure as in my example, the orange range with formulas and the green range the named range.
Have a look

Here is my sheet 1 where I have numbered the order with respect to the consolidated sheet

New Microsoft Excel Worksheet.xlsx
ABCDEFGHIJKLMNOPQR
1123456789101112131415161718
2IFSCABINVOICE NOPO#BOXUNIT NOLR NOVEHICLE TYPEVEHICLE NOLR DATEFTY IN TIMEFTY OUT TIMEDATESEAL NODRIVER NAMEMOBLI NORR NumberVENDOR
3990322ABCDE20877563211610121123ABC12327-03-202227-03-202227-03-202227-03-2022U1.32.11Max123456789065432ABS
42087766321172318:3019:30
520877763211855
6208778632119123
720877963212054
820878063212122
92087816321227
102087826321235
Sheet 1


Here is the Sheet 2 where once again I have numbered the order with respect to the consolidated sheet

New Microsoft Excel Worksheet.xlsx
ABCDEFGHIJKLMNOPQR
1123459678141011121315161718
2IFSCABINVOICE NOPO#BOXVEHICLE NOUNIT NOLR NOVEHICLE TYPESEAL NOLR DATEFTY IN TIMEFTY OUT TIMEDATEDRIVER NAMEMOBLI NORR NumberVENDOR
3990322ABCDE5678999876510ABC123121123U1.32.1127-03-202227-03-202227-03-202227-03-2022Max123456789065432UYT
4567900987662318:3019:30
55679019876755
656790298768123
75679039876954
85679049877022
9567905987717
10567906987725
11
12
13990365DFGHJK457756321161DEF123104567U2.32.1127-03-202227-03-202227-03-202227-03-2022Max123456789012345XYZ
1445776632117318:3019:30
15457776321184
164577863211954
174577963212011
184578063212122
194578163212265
20457826321239
21
22
Sheet2



Yet I get the error shown in the image attached below
 

Attachments

  • IMG 6.png
    IMG 6.png
    91.7 KB · Views: 7
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top