Copy Identical Ranges from All Workbooks in a Folder

[How_Do_I]

I have 12 workbooks in a folder.

I want to copy ranges from these workbooks to a sheet in a new workbook.

I will then have formulas looking at this data...

The ranges I copied will be subject to an update therefore, when I update the sheet in my new workbook I still want my formulas to work.

Any starters on this one please?

 

[jbeaucaire]

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.

In the "This section to customize" is where you would copy the specific range from each workbook into your master sheet.

 

[How_Do_I]

Thank you very much indeed Jerry, I'll have a look at that and once I've changed it to suit, I'll see what happens in my workbook!

 

[How_Do_I]

I'm trying to do this with some "simple" code I found to give me an idea of what is going on but I'm already having problems.

The data I'm trying to copy is:

FT Scores

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 141px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD><TD>AD</TD><TD>AE</TD><TD>AF</TD><TD>AG</TD><TD>AH</TD><TD>AI</TD><TD>AJ</TD><TD>AK</TD><TD>AL</TD><TD>AM</TD><TD>AN</TD><TD>AO</TD><TD>AP</TD><TD>AQ</TD><TD>AR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=25>Half Time</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=8>Home</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=8>Away</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=9>Overall</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">D</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">L</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">F</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">A</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">GD</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Pt</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">D</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">L</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">F</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">A</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">GD</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Pt</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">-</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">D</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">L</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">F</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">A</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">GD</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Pt</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ffff00">Barnsley</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">-6</TD><TD style="TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">-8</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="TEXT-ALIGN: center">46</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: center">-14</TD><TD style="TEXT-ALIGN: center">44</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The results I get is:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 139px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 41px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=25>Half Time</TD><TD style="FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=8>Home</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=8>Away</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold" colSpan=9>Overall</TD><TD style="FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">D</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">L</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">F</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">A</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">GD</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Pt</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">D</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">L</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">F</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">A</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">GD</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Pt</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">-</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">W</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">D</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">L</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">F</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">A</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">GD</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Pt</TD><TD style="FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Barnsley</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">-6</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">-8</TD><TD style="TEXT-ALIGN: right">23</TD><TD> </TD><TD style="TEXT-ALIGN: right">46</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">33</TD><TD style="TEXT-ALIGN: right">-14</TD><TD style="TEXT-ALIGN: right">44</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The code I'm using is:

Sub CopyRangeValues()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim rnum As Long
    Dim i As Long
    Dim a As Long
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Users\Gus\Desktop\Tables\25"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            rnum = 4
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                Set sourceRange = mybook.Worksheets("FT Scores").Range("S4:As27")
                a = sourceRange.Rows.Count
                With sourceRange
                    Set destrange = basebook.Worksheets(1).Cells(rnum, 1). _
                                    Resize(.Rows.Count, .Columns.Count)
                End With
                destrange.Value = sourceRange.Value
                mybook.Close
                rnum = i * a + 1
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Why are my columns in the Jeanie pics not aligned please?

 

[How_Do_I]

OMG, What a plonker I am, after reading my thread PROPERLY I noticed the column headers are my problem...!