Copy Identical Ranges from All Workbooks in a Folder

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,817
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
I have a macro that may be pretty close to "ready to use" for collecting data from all files in a specific folder.

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

Well-known Member
Joined
Oct 23, 2009
Messages
1,817
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,817
Office Version
  1. 2010
Platform
  1. Windows
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:

Code:
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,817
Office Version
  1. 2010
Platform
  1. Windows
OMG, What a plonker I am, after reading my thread PROPERLY I noticed the column headers are my problem...! :ROFLMAO:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,074
Messages
5,835,232
Members
430,349
Latest member
Gregori

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top