vba 将不同工作簿的范围复制到一个最终目标工作表中

Copying ranges from different workbooks into one final destination sheet

提问by RocketGoal

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.

我将根据位于多个工作簿中的大量数据生成一些图表。数据在所有工作簿中的格式完全相同，并驻留在同一级别的文件夹中。我将把数据的一部分（范围）放入一个最终的工作簿中，我将从那里生成我的图表。

I've looked around for examples, and tried Excel help files.

我四处寻找示例，并尝试使用 Excel 帮助文件。

Lots of things seem to be wrong.

很多事情似乎都错了。

Also, how do you add the name of the file that the ranges came from in Column B on the same rows?

另外，如何在同一行的 B 列中添加范围来自的文件的名称？

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

The outcome of the process would look like the image below but without the colours or the additional "_b":

该过程的结果如下图所示，但没有颜色或额外的“_b”：

回答by Dick Kusleika

I don't know if this is exactly what you want, but I think it will get you closer and give you some clues on how to proceed. We can edit it to make it right.

我不知道这是否正是你想要的，但我认为它会让你更接近并为你提供一些关于如何进行的线索。我们可以编辑它以使其正确。

Sub CopySourceValuesToDestination()

    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim sDestPath As String
    Dim sSourcePath As String
    Dim shDest As Worksheet
    Dim rDest As Range
    Dim vaFolder As Variant
    Dim vaFiles As Variant
    Dim i As Long

    'array of folder names under sDestPath
    vaFolder = Array("ABC", "DEF", "GHI", "JKL")

    'array of file names under the respective folders in vaFolder
    vaFiles = Array("ABCFile.xls", "DEFFile.xls", "GHIFile.xls", "JKLFile.xls")

    sDestPath = "S:\Common\XYZ\Michael S\Macrotest\"
    sSourcePath = "S:\Common\XYZ\Michael S\Macrotest\"

    'Open the destination workbook at put the destination sheet in a variable
    Set wbDest = Workbooks.Open(sDestPath & "Destination.xls")
    Set shDest = wbDest.Sheets(1)

    'loop through the folders
    For i = LBound(vaFolder) To UBound(vaFolder)
        'open the source
        Set wbSource = Workbooks.Open(sSourcePath & vaFolder(i) & "\" & vaFiles(i))

        'find the next cell in col C
        Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
        'write the values from source into destination
        rDest.Resize(5, 1).Value = wbSource.Sheets(1).Range("C4:C8").Value

        'repeat for next source range
        Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
        rDest.Resize(5, 2).Value = wbSource.Sheets(1).Range("C17:D21").Value

        wbSource.Close False
    Next i

End Sub