title: Copying ranges from different workbooks into one final destination sheet
body: <p>I'm going to be generating some graphs from a lot of data located in multiple workbooks. The data is formatted exactly the same in all workbooks and reside in folders all at the same level. I'm going to be bringing parts (ranges) of the data into one final workbook where I'll generate my graphs from.</p>
  
  <p>I've looked around for examples, and tried Excel help files.</p>
  
  <p>Lots of things seem to be wrong.</p>
  
  <p>Also, how do you add the name of the file that the ranges came from in Column B on the same rows?</p>
  
  <pre><code>Sub CopySourceValuesToDestination()
  
  Dim DestPath As String
  Dim SourcePath As String
  Dim Folder As Variant
  Dim Folders As Variant
  Dim FileInFolder As Variant
  Dim Range1 As Range
  Dim Range2 As Range
  Dim DesitnationPaste1 As Variant
  Dim DesitnationPaste2 As Variant
  
  Folder = Array("ABC", "DEF", "GHI", "JKL")
  FileInFolder = Array("ABCFile", "DEFFile", "GHIFile", "JKLFile")
  
  ''My final Excel file sits in the parent folder of the source files folders
  DestPath = "S:\Common\XYZ\Michael S\Macrotest\"
  
  ''Each file has it's own folder, and there are many specific files in each
  SourcePath = "S:\Common\XYZ\Michael S\Macrotest\ + Folder"
  
  ''Always the same in each of my source files
  Range1 = Cells("C4:C8") 
  Range2 = Cells("C17:D21") 
  
  ''Below I 'm trying to paste Range1 into Column C directly under the last used cell
  DestinationPaste1 = Range("C5000").End(xlUp).Offset(1, 0)
  
   ''Below I 'm trying to paste Range2 into Column D directly under the last used cell
  DestinationPaste2 = Range("D5000").End(xlUp).Offset(1, 0)
  
  ''Trying to make it loop through the folder and the_
  ''files...but this is just a guess
  For Each Folder In Folders 
  ''Again a guess
  F = 0 
  
  ''The rest of the process would open a source file copy_
  ''Range1 and then opening the Destination file and pasting_
  ''it in the Row 1 of Column C. Hopefully it then goes back_
  ''to the open source file copies Range2 and pastes it the_
  ''next Row down in Column C
  
      Workbooks.Open FileName:=SourcePath + FileName + "Source.xls"
  
          Workbook.Sheet(Sheet2).Range1.Copy
  
      Workbook.Open FileName:=DestPath + "Destination.xls"
  
          Workbook.Sheet(Sheet1).DestinationPaste.Select
              Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
              Operation:= xlNone, SkipBlanks:=False, Transpose:=True
  
      Windows(SourcePath + FileName + "Source.xls").Activate
  
          Workbook.Sheet(Sheet2).Range2.Copy
  
      Workbook.Open FileName:=DestPath + "Destination.xls"
  
          Workbook.Sheet(Sheet1).DestinationPaste.Select
              Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
              xlNone, SkipBlanks:=False, Transpose:=True
  
    Windows(SourcePath + FileName + "Source.xls").Activate
      ActiveWorkbook.Close
  F = F + 1
  Next
  
  End Sub
  </code></pre>
  
  <p>The outcome of the process would look like the image below but without the colours or the additional "_b":</p>
  
  <p><a href="https://i.stack.imgur.com/6q3S6.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6q3S6.jpg" alt="Column B in the spreadsheet has three letter values like &#39;ABC&#39; that get repeated. Column C has the same values followed by a number indicating how many times it has appeared. For example, &#39;ABC1&#39;, &#39;ABC2&#39;, &#39;DEF1&#39;, etc."></a>
  </p>