Copy Identical Ranges from All Workbooks in a Folder

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,801
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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,801
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,801
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,801
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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