Copy same range from multiple XL files

[izzyq]

How would I go about writing a macro that would allow me to to go through 10-12 XL files in a directory and copy the same range,A2-M200 for example, from all of them and to a new workbook or sheet. Help Me Obi Wan Kenobi, You're My Only Hope.

 

[SkipVought]

izzyq,

I did not have time to test this out, but it should be pretty close. It assumes that all the workbooks are open AND that the active workbook and active sheet are where you want the data written. Put this procedure in THAT workbook in a module...

Sub CopyDataFromWorkbooks()
    Dim wbk As Workbook, wks As Worksheet, rng As Range, Cell As Range
    Dim wsMaster As Worksheet, lMasterRow As Long
    Set wsMaster = ActiveWorkbook.ActiveSheet
    For Each wbk In Workbooks
        If wbk.Name <> &quot;Master.xls&quot; Then
            For Each wks In wkb
                Set rng = wbk.Range(&quot;A2:M200&quot;)
                lMasterRow = wsMaster.UsedRange.Rows.Count + 1
                For Each Cell In rng
                    With Cell
                        wsMaster.Cells(lMasterRow + (Cell.Row - rng.Row), .Column).Value = .Value
                    End With
                Next
            Next
        End If
    Next
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Hey, izzyq,

Change the If statement to...

        If wbk.Name <> wsMaster.Parent.Name Then

Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

izzyq,

I tested and made a couple of modifications. Notice that I am using .UsedRange instead you your fixed range. Go ahead and put your range back in if you want.

Sub CopyDataFromWorkbooks()
    Dim wbk As Workbook, wks As Worksheet, rng As Range, Cell As Range
    Dim wsMaster As Worksheet, lMasterRow As Long
    Set wsMaster = ActiveWorkbook.ActiveSheet
    For Each wbk In Workbooks
        If wbk.Name <> wsMaster.Parent.Name Then
            For Each wks In wbk.Worksheets
                Set rng = wks.UsedRange
                lMasterRow = wsMaster.UsedRange.Rows.Count + 1
                For Each Cell In rng
                    With Cell
                        wsMaster.Activate
                        wsMaster.Cells(lMasterRow + (.Row - rng.Row), .Column).Value = .Value
                    End With
                Next
            Next
        End If
    Next
End Sub

Hope this does the trick Skip,
metzgsk@voughtaircraft.com

 

[izzyq]

Thanks for the start, much appreciated.

Is there any way that you know of whereby I can don't need to have the XL files open. I'm lookin got have the macro search through the directory of the 10-12 excel files, grab the fixed ranged from each of the 10-12 files and paste that information in a new workbook. Help Me Obi Wan Kenobi, You're My Only Hope.