Databases
StackOverflow2013
Postsdbo
POExcel VBA: automating copying ranges from different workbooks into one final destination sheet?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA: automating copying ranges from different workbooks into one final destination sheet?
    primarykey
    data
    text
    <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>This made me think that this sort of thing is ripe for VBA automation. Only problem, I'm a novice. I've tried writing pseudo code and then replacing it with what I think is correct VBA. I've looked around for examples, and tried Excel help files, but I'm missing some important steps somewhere...and some basic steps as well.</p> <p>Lots of things seem to be wrong (... at least you'll have something to smile about before the weekend). If anyone can point out where my brain has abandoned me, I'd be very grateful.</p> <p>Also, <em>how do you add the name of the file that the ranges came from in Column B on the same rows?</em> This is something that would really help me but I can't find an example of how to do it.</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 additonal "_b":</p> <p><a href="http://i51.tinypic.com/14sm6ac.jpg" rel="nofollow noreferrer">Final Data Output http://i51.tinypic.com/14sm6ac.jpg</a></p> <p>Many thanks again for any help you can give me.</p> <p>Michael.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRocketGoal
    1. USRocketGoal
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel VBA: automating copying ranges from different workbooks into one final destination sheet?
      1. USDick Kusleika
      1. VTFavorite
    2. VO
      singulars
      1. POExcel VBA: automating copying ranges from different workbooks into one final destination sheet?
      1. USgagneet
      1. VTFavorite
    3. VO
      singulars
      1. POExcel VBA: automating copying ranges from different workbooks into one final destination sheet?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload