Results 1 to 3 of 3

Thread: VBA help in creating a consolidation spread sheet, for multiple files in one folder

  1. #1
    Junior Member Baja524's Avatar
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    VBA help in creating a consolidation spread sheet, for multiple files in one folder

    I currently have "and growing" aprox 100 spreadsheets in one folder named 2013. The spreadsheets have multiple sheets and I would like to consolidate data from selected cells in each spreadsheet and all on sheet1 from each file.
    So for instance in folder 2013 there are lets say sheets named "HomeDepot" and "Lowes" etc... Home depot and Lowes are identical spreadsheets. They are pasword protected and have aprox 11 sheets, I would like to compile the data from "Home depot" spreadsheet tab 1 cell a6 and b6 onto one master spreadsheet. and the same data from the Lowes spreadsheet etc.... through all the files in the folder

    Please help!

  2. #2
    Junior Member Baja524's Avatar
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    here is what I have been trying to work with, Need to only open certain cells not whole page and each file that opens I have to click no on saving large amounts on clipboard because I suppose the file is coping the whole sheet?

    Sub ConsolidateAll()

    Dim wkbConsol As Workbook
    Dim wksConsol As Worksheet
    Dim wkbOpen As Workbook
    Dim wksOpen As Worksheet
    Dim FolderName As String
    Dim FileName As String
    Dim Cnt As Long

    Application.ScreenUpdating = False

    Application.StatusBar = "Please wait..."

    Set wkbConsol = ActiveWorkbook
    Set wksConsol = wkbConsol.Worksheets(1)

    'Change the path accordingly
    FolderName = "C:\Users\eeem\Desktop\2013\"

    If Right(FolderName, 1) <> "\" Then FolderName = FolderName & "\"

    FileName = Dir(FolderName & "*.xls")

    Cnt = 1
    Do While FileName <> ""
    If FileName <> wkbConsol.Name Then
    Application.StatusBar = "Opening " & FileName & "..."
    Set wkbOpen = Workbooks.Open(FolderName & FileName)
    Set wksOpen = wkbOpen.Worksheets(1)
    Application.StatusBar = "Copying the data from " & FileName & "..."
    With wksOpen.UsedRange
    If Cnt = 1 Then
    .Copy
    wksConsol.Cells(1, "A").PasteSpecial Paste:=xlPasteValues
    Else
    .Offset(1, 0).Resize(.Rows.Count - 1).Copy
    wksConsol.Cells(wksConsol.Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    End If
    End With
    wkbOpen.Close savechanges:=False
    Application.StatusBar = FileName & " closed..."
    End If
    FileName = Dir
    Cnt = Cnt + 1
    Loop

    Application.StatusBar = False

    Application.ScreenUpdating = True

    End Sub

  3. #3
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Since it would be too timeconsuming to open 100 files (and growing) i'd suggest you use a ADO-connection to retrieve the data from the closed workbooks.

Similar Threads

  1. Replies: 1
    Last Post: 06-07-2013, 10:32 AM
  2. Moving Multiple Files From One Folder To Another
    By galang_ofel in forum Excel Help
    Replies: 5
    Last Post: 05-10-2013, 12:43 AM
  3. Replies: 2
    Last Post: 09-24-2012, 09:20 PM
  4. Count Files In A Folder VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-07-2011, 10:57 PM

Posting Permissions

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