Results 1 to 7 of 7

Thread: Consolidate Multiple Workbooks Into One And Push Back Down Updated Data

  1. #1
    Junior Member
    Join Date
    Apr 2014
    Posts
    4
    Rep Power
    0

    Consolidate Multiple Workbooks Into One And Push Back Down Updated Data

    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)
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Welcome to board!!!

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

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Apr 2014
    Posts
    4
    Rep Power
    0
    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

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Apr 2014
    Posts
    4
    Rep Power
    0
    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

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    Last edited by Excel Fox; 04-08-2014 at 07:38 AM. Reason: Edit: Modified URL and Question
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Apr 2014
    Posts
    4
    Rep Power
    0
    hello- yes, for protection of specific filenames i have removed the exact filepath...but it does reference Microsoft Sharepoint files

Similar Threads

  1. Replies: 1
    Last Post: 06-07-2013, 10:32 AM
  2. Replies: 1
    Last Post: 05-09-2013, 08:56 AM
  3. Consolidate multiple workbooks from a folder into one master file VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-26-2013, 09:00 PM
  4. Replies: 2
    Last Post: 12-19-2012, 08:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •