Excel macro for consolidating workbooks

Description & " Procedure Merge Lists" Resume Before Exit End Sub Like the previous macro this one requires a vertical list starting in cell A1 on both sheet 1 and 2.

For Each r Cell In r A With r Cell If Worksheet Function. Value) = 0 Then l Count = l Count 1 v Result(l Count, 1) = . Color = 15000804 Next End With Set r Companies = r Companies.

Value Else l Count2 = l Count2 1 v Result2(l Count2, 1) = . Value = "E-mail" End Select Next End With l Result Rows Then Exit For .

This example requires some values (text or numbers) in cell A1 and down on sheet 1 and 2.

Copy the code by selecting it with the mouse, press CTRL C to copy and paste into a VBA module with CTRL V. Auto Fit End With Before Exit: Set r Persons = Nothing Set r Companies = Nothing Set col Companies = Nothing Erase v Result Erase v Persons Erase v Contacts Exit Sub Error Handle: Msg Box Err.

'The values will be stored in two arrays that will 'be copied to the spreadsheet in one swift 'operation.

At this point in time we don't know 'how many values we will find, so we just dimension 'the arrays to have as many rows as the two 'ranges combined.

The table with contact persons (in "persons.xls") has the following fields/columns: Contacts | Company | Tel.

| E-mail The table with companies has the following fields/columns: Company | Address | Postal code | City | Type | Info To put it short the macro loops the two tables, and if the company name matches, the data for the contact person(s) is appended.

Offset(0, 5)) Else Set r Companies = Range(Range("A2"), Range("A2").

Offset(0, 5)) End If 'By adding company names as keys to a collection, we get 'a list with no duplicates.

If a name already in the 'collection is added once more it triggers an error, and 'that is the reason for the next line.

Tags: , ,