Consolidate Multiple Workbooks Into One And Push Back Down Updated Data

**myexcellent123** · 04-06-2014, 07:00 PM

Hello! First of all, thank you in advance to anyone reading this!

So, here is my situation, I have a set list of Unique IDS and standard row of same, header columns across multiple workbooks. Alll of these are saved on a central Sharepoint webpage.

These wkbs are split between 3 depts. I have a total of about 275 columns and 1300 Unique IDs.

The first dept has split the Unique Ids and updates across all columns. Easy to merge and i have the macro that accomplishes this. Basically it opens all files and copies and adds one wkb beneath each other into one single sheet.

The second dept makes it complicated, as it has split COLUMNS (vs. Unique IDS) and multiple people are updating the same Unique ID but for different columns.

The third dept (single person) works on the comprehensive list of all Unique IDs but has his own section of columns.

So, the challenge is:

1.To merge all files across all dept into ONE MASTER WKB. I could do this in Access (linked via Unique ID) but it exceeds the 255 column limit, as this would go out to 275 columns.

.....then with all *updated* data off these files

2.To push all *updated* data back out to the individual files.

The attached sample Wkb might make this easier to understand. Please assume that each tab in this file is a separate workbook.

I truly appreciate any help on this!

i also posted this on (Consolidate multiple Wkbs into ONE-update-then push back down updated data | Chandoo.org Excel Forums - Become Awesome in Excel)

**Admin** · 04-06-2014, 10:11 PM

Hi

Welcome to board!!!

Try the possible answers at the bottom of this page. Come back if it doesn't help you at all.

**myexcellent123** · 04-06-2014, 11:50 PM

Hi Admin, thank you very much and yes, i did review the posts/links below. Unfortunately, those dont aid is what I am working with. Since my complication is that I am trying to merge data from multiples files but its not a simple row consolidation. Its dealing with duplication of Unqiue IDs across files while trying to consolidate Rows & Columns.

The second issue is trying push all updated back into individual files

**Excel Fox** · 04-07-2014, 08:43 PM

myexcellent123, are these workbooks hosted on a teamsite in SharePoint, or do you have access to a folder which contains all these workbooks? If you only have the SharePoint links, and not a folder path, then can you post the code you already have.

**myexcellent123** · 04-07-2014, 11:22 PM

Hi ExcelFox- yes all of the files are on a Sharepoint. What i have to this point- only consolidates the files from the First Dept, as the are split by column A on Unique IDs.

Code:

 

Sub mainConsolidation()

With Excel.Application
        .ScreenUpdating = False
        .Calculation = Excel.xlCalculationManual
        .EnableEvents = False
End With
Dim a As Integer

Workbooks("CONSOLIDATE MASTER.xlsm").Worksheets("Sheet1").Range("A2:JP10000").ClearContents ' 
Call File2 
Call File3
all Blank

With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlAutomatic
        .EnableEvents = True
End With
End Sub

Sub blank()

Dim mr As Range
Dim ict As Long

Set mr = ActiveSheet.UsedRange

For ict = mr.Rows.Count To 1 Step -1

If Application.CountA(Rows(ict).EntireRow) = 0 Then
Rows(ict).Delete
End If

Next ict

End Sub
Sub File2() 
Dim arange As String
Dim a As Integer

Dim cell As Object
Dim wb As Workbook
Application.DisplayStatusBar = True
Set wb = Workbooks.Open("Sharepoint /File2.xlsm") 
Workbooks("File2.xlsm").Worksheets("Sheet1").Activate 
ActiveSheet.Cells.EntireColumn.Hidden = False
ActiveSheet.Cells.EntireRow.Hidden = False
With ActiveSheet
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With

a = Workbooks("CONSOLIDATE MASTER.xlsm").Worksheets("Sheet1").UsedRange.Rows.Count

Dim rc1 As Integer
rc1 = Workbooks("File2.xlsm").Worksheets("Sheet1").Range("A10000").End(xlUp).Row ' ptn column is A
Dim rc2 As Integer
rc2 = Workbooks("CONSOLIDATE MASTER.xlsm").Worksheets("Sheet1").Range("A:A").SpecialCells(xlLastCell).Row - 1

arange = "A2:JP" & rc1 
Workbooks("File2.xlsm").Worksheets("Sheet1").Range(arange).Copy
Workbooks("CONSOLIDATE MASTER.xlsm").Worksheets("Sheet1").Range(arange).PasteSpecial Paste:=xlPasteValues

Workbooks("File2.xlsm").Close savechanges:=False
End Sub

**Excel Fox** · 04-08-2014, 07:21 AM

When you say SharePoint, do you mean Microsoft SharePoint or do you mean a shared folder somewhere on a server or local network like How To Share Files and Folders Over a Network

The reason why I am asking is because the path you've posted above doesn't suggest that your files are on a Microsoft SharePoint. Or have you editted it just for keeping its identity.