Excel 2007 : Combining data from multiple Excel Files

Here is the question...

I have select data entries in multiple (=52*7) excel spreadsheets (separate files) that I need to collect into 1 spreadsheet. The format of the source table is almost always the same, and the title row of the column that I need to retrieve data from IS ALWAYS the same. Is there a way to save myself some time here, or am I destined to spend the next few hours in copy-paste world?

Thanks,
Rob

Sweet. Thanks. Now, while I'm not a noob to programming generally, I am a noob to programming VBA scripts in Excel. Any advice on where to go to get a quick primer on where I should put the code he has and how to call it? I don't need the "press ALT+F11 to open the VBA editor," tutorial, but I do need the "here is where you should put your code" and the "here is how you will call your code" tutorials. Any advice?

Thanks again Pike,

One question that I have when I open up the VBA editor, is where to insert the code that you copied - "where" meaning, do I create a new module? If so, where should the module be located, etc.

I wish that I could provide someone out there a copy of the data that I'm working with, but I'm sure you are all used to data in excel spreadsheets being sensitive. Consequently, I've attached "sample.xls" from which I've stripped of all underlying data. As you can see, There are weekly copies of identical spreadsheets. The workbooks contain more than 1 worksheet, but the sheet that I want to use from each workbook is always the first sheet in the workbook.

I've also attached "sample2.xlsx" to demonstrate what data I'd like to glean from the Sample.xls spreadsheet - what I envision it looking like when it works properly. Where there are formulas, the formula is the actual formula that exists in the spreadsheets. In all other circumstances I've simply inserted filler data so that you can get a picture as to what the spreadsheet is supposed to look like.

I expect that up until now, it is pretty straight forward (although I really don't know). I'll throw a small kink into the works. First, it is possible that there could be more than 3 levels (e.g. a copy of level 3 with two different prices) which potentially adds an extra row. Also, the titles (blue cells) shown in columns L-X may change (e.g. if "Center" is YYYY instead of XXXX, or for other reasons). Nonetheless, for columns A-J, the column titles will always stay the same, and for columns L-X, as you can tell from the 'Count' spreadsheet of Sample2, it is OK to simply copy the title of the column (The single row directly above the numerical data) into the "Type" column. The problem is that there could be a greater or fewer number of "Types" than what is shown in Sample.xls (it could run from A-M and F1-J2 instead of A-E and F1-G8 as shown).

I believe that is an accurate run down of all of the work that I have before me. Hopefully you guys have a kind heart and are willing to spend a little bit of time helping me out with this.

Thank you,
Rob

Thanks Pike,

The problems begin early. I used Sample2.xlsm and created a new module that I added JB's code to. It unfortunately quickly fails and gives me a "Run-time error '9': Subscript out of range" error. When I debug, the following line of code is highlighted.

 Sheets("Master").Activate   'sheet report is built into

Any ideas?

Thanks,
Rob

Hi robbyvegas,

I know it was a few years ago but do you remember how you overcame the problem you had here?
I am trying to do something almost identical to what you were doing but am having a little bit of trouble getting going!

Thanks
Alex

OK. Working on it. Out of curiosity. What is the best VBA book out there?

OK, I'm working on this task again and I have a few thoughts that might simplify the tasks if you can help me figure out how to implement them.

First, I believe that I will always need B3, G24, and J24 of each spreadsheet. Can you help me to amend the code

  'This is the section to customize, replace with your own action code as needed
        'Find last row and copy data
            LR = Range("A" & Rows.Count).End(xlUp).Row
            If NR = 1 Then      'copy the titles and data
                Range("A1:A" & LR).EntireRow.Copy _
                    wbknew.Sheets("Master").Range("A" & NR)
            Else                'copy the data only
                Range("A2:A" & LR).EntireRow.Copy _
                    wbknew.Sheets("Master").Range("A" & NR)
            End If

to pick up these two cells from each spreadsheet?

Second, I think that there is a command to have excel create separate worksheets where you have white space between sets of data. Is that correct? If so, can you remind me of what function will do this (if I'm thinking of a macro that you are familiar with and not a function, please let me know)?

Thank you,

Third, using this great code to pull all of these separate files together, is there then a way to automatically transpose all of the rows to columns, and putting all of the data into colums A & B?

I think that these three things would go a long way towards simplifying my life tonight.

Thank you again for all of the help on this.

I could really use some help ASAP if anyone knows how to do this...

I'm trying to use the code that Pike posted for me above as follows:

 'Sub Consolidate()
'Author:     Jerry Beaucaire'
'Date:       9/15/2009     (2007 compatible)
'Summary:    Open all Excel files in a specific folder and merge data
'            into one master sheet (stacked)
'            Moves imported files into another folder
Dim fName As String, fPath As String, fPathDone As String, OldDir As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wbkNew As Workbook

'Setup
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
   
    Set wbkNew = ThisWorkbook
    wbkNew.Activate
    Sheets("Master").Activate   'sheet report is built into
   
    If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
   
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        Cells.Clear
        NR = 1
    Else
        NR = Range("A" & Rows.Count).End(xlUp).Row + 1
    End If

'Path and filename (edit this section to suit)
     MsgBox "Please select a folder with files to consolidate"
    Do
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                fPath = .SelectedItems(1) & "\"
                Exit Do
            Else
                If MsgBox("No folder chose, do you wish to abort?", _
                    vbYesNo) = vbYes Then Exit Sub
            End If
        End With
    Loop
    fPathDone = fPath & "Imported\"     'remember final \ in this string
    On Error Resume Next
        MkDir fPathDone                 'creates the completed folder if missing
    On Error GoTo 0
    OldDir = CurDir                     'memorizes the users current working path
    ChDir fPath                         'activate the filepath with files to import
    fName = Dir("*.xls")                'listing of desired files, edit filter as desired

'Import a sheet from found file
    Do While Len(fName) > 0
        If fName <> wbkNew.Name Then     'make sure this file isn't accidentally reopened
        'Open file
            Set wbData = Workbooks.Open(fName)

        'This is the section to customize, replace with your own action code as needed
        'Find last row and copy data
        
          Range("B3").Copy wbkNew.Sheets("Master").Range("A" & NR)
          Range("G24").Copy wbkNew.Sheets("Master").Range("B" & NR)
          Range("J24").Copy wbkNew.Sheets("Master").Range("C" & NR)
         
         
         'LR = Range("A" & Rows.Count).End(xlUp).Row
           ' If NR = 1 Then      'copy the titles and data
          '      Range("A1:A" & LR).EntireRow.Copy _
          '          wbkNew.Sheets("Master").Range("A" & NR)
           ' Else                'copy the data only
           '     Range("A2:A" & LR).EntireRow.Copy _
           '         wbkNew.Sheets("Master").Range("A" & NR)
           ' End If
        
        'close file
            wbData.Close False
        'Next row
            NR = Range("A" & Rows.Count).End(xlUp).Row + 1
        'move file to IMPORTED folder
            Name fPath & fName As fPathDone & fName
        'ready next filename
            fName = Dir
        End If
    Loop

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
    ChDir OldDir                             'restores users original working path
End Sub

I believe that I'm specifically having trouble with the following bit:

  'This is the section to customize, replace with your own action code as needed
        'Find last row and copy data
        
          Range("B3").Copy wbkNew.Sheets("Master").Range("A" & NR)
          Range("G24").Copy wbkNew.Sheets("Master").Range("B" & NR)
          Range("J24").Copy wbkNew.Sheets("Master").Range("C" & NR)

It runs fine, but the returned values are mostly #REF because they refer to items that are calculated from other values in the source worksheets. Is there a way to do the equivalent of a paste special to paste over just the values of the data that I'm copying?

Thanks,
Rob

Ah! Thank you! That works.

Now I need to figure out how to copy L6:L10 - DD6:DD10 and to transpose their values and paste it into a new worksheet (Master2?). Can you guys help?

Thank you both!

Rob

I apologize. Please see the attached files.

In Sample.xls, I'd like to copy L6:L10 - DD6:DD10, and L16:L20 - DD16:DD20. Then as shown in the Count worksheet of Sample2.xlsx I'd like to transpose the matrices of copied data, and to paste the values of these cells into the columns 2-5 of a Master2 worksheet, consecutively for each excel file in the folder.

Thanks again. I really appreciate the help. Please let me know if any of what I'm saying doesn't make sense.

Thank you. I suppose you don't see a correlation because I don't know how to check to see if there is an empty column. DD is the furthest that any of my workseets goes to. Is there a more graceful way to approach this?

Thanks again.

I'm currently copying to column DD because some of the worksheets that I need to process have data all the way out to that column. Is there a way to automatically detect when no row of the selection contains a value? That would be the more elegant way to approach this I suppose.