Compile data from several worksheets to one.

[jdbaba]

Dear All,

I have like 100's of worsheet with the data. But I want to compile all the data into one worksheet. All the worksheets are almost similar. I just want to compile the particular column data of all the worksheets.

SUppose I have a worksheet name 10.15 and I have the data on E,F, and G column and I have other worksheet 10.20 and have the same type of data on the same columns but the row number changes a bit. NOw, I want to continuously arrange the data from these worksheets in one worksheet. Suppose the worksheet 10.15 has 15 rows and the worksheet 10.20 has 20 rows, I want a new spreadsheet with 25 rows which contains the data of both the worksheets.

Hope you good people know my problem.

Please help soon. Thanks.

Best Regards,
Jdbaba

[MarvinP]

How do you get 25 rows when the first had 15 and the second had 20?

[jdbaba]

I meant 35 rows for the new one.

[MarvinP]

I kind of knew that, but I needed to know you weren't using some new math on us.

So you want to grab a column of data from a bunch of worksheets and stick them on the bottom of a single worksheet. I kind of have the idea. Are all these worksheets in different workbooks? As in different .xlsx files or are they all in the same workbook?

To do this problem I need a small sample file. I would turn on the macro recorder and do about 2 of the copy and paste columns by hand. Then I'd look at the Macro code that is recorded to see what Excel VBA thought I'd done. Then I'd find the step that copied "on the bottom" of the column and change this code from a fixed Range to a variable. Test the new code and send it back to you to figure out.

Is that what you want? Can you supply a sample file(s)?

[jdbaba]

Dear MarvinP,

I have attached the sample spreadhseet with three sheets. What I meant by 100's of worksheets is like the sheets of the same spreadsheets (i.e single *.xls).

Here in the file, there are 4 columns of data starting from the same point in each of the worksheets but they necessarily don't end on the same row. So what I want is on the new sheet to list all the data continuously. I hope you understand. Thanks in advance.

[JBeaucaire]

'WORKBOOKS TO 1 SHEET
I have a macro that may be pretty close to "ready to use" for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

[JBeaucaire]

Here's an edited version of the macro that will work with the non-standard layout represented on your sheets. If the data on each sheet started at A1 instead of C2, these edits wouldn't be necessary.

Option Explicit

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)
    fPath = "C:\2010\Test\              'remember final \ in this string"
    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
            LR = Range("C" & Rows.Count).End(xlUp).Row
            If NR = 1 Then      'copy the titles and data
                Range("C2:F" & LR).EntireRow.Copy _
                    wbkNew.Sheets("Master").Range("A" & NR)
            Else                'copy the data only
                Range("C3:F" & LR).EntireRow.Copy _
                    wbkNew.Sheets("Master").Range("A" & NR)
            End If
        'close file
            wbData.Close False
        'Next row
            NR = Range("C" & 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

Put this macro into an empty workbook and name one sheet Master.
Edit the fPath variable to point to the correct folder. Then it should work fine.