extracting data from multiple sheeted excel workbooks into one single sheeted workbook

Question

I have 50 excel workbooks each containing 5 sheets inside. They all have the same structure, same sheet names, same column titles. I need to extract the 4th sheet from each file and put data in one single sheeted workbook under each other. I found this macro but it extracts on different sheets. I can't figure out how to modify this code to fit my needs. Can someone please advise?

Sub CombineWorkbooks() 
Dim FilesToOpen 
Dim x As Integer 
On Error GoTo ErrHandler 
Application.ScreenUpdating = False 
FilesToOpen = Application.GetOpenFilename _ 
              (FileFilter:="Microsoft Excel Files (*.xlsx), *.xlsx", _ 
               MultiSelect:=True, Title:="Files to Merge") 
If TypeName(FilesToOpen) = "Boolean" Then 
    MsgBox "No file is chosen" 
    GoTo ExitHandler 
End If 
x = 1 
While x <= UBound(FilesToOpen) 
    Workbooks.Open Filename:=FilesToOpen(x) 
    Sheets("Associates report").Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    x = x + 1 
Wend 
ExitHandler: 
    Application.ScreenUpdating = True 
    Exit Sub 
ErrHandler: 
    MsgBox Err.Description 
    Resume ExitHandler 
End Sub code here

Jerry Beaucaire Jerry Beaucaire · Accepted Answer · 2012-08-06T14:31:17

Here's a macro 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. In the "this is the section to customize", the code:

LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)

...would need to be something like this to copy from sheet 4:

LR = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row  'Find last row
Sheets("Sheet4").Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)

Or looking at your sample code above, maybe:

LR = Sheets("Associates Report").Range("A" & Rows.Count).End(xlUp).Row  'Find last row
Sheets("Associates Report").Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)

It's intended as a generic starting point, you will have to go through and edit for your environment. Check the comments.

extracting data from multiple sheeted excel workbooks into one single sheeted workbook

1 Answers