将多个Excel表单中的数据合并到主表单 [英] Merging Data from Multiple Excel Forms to Master Form

问题描述

我需要创建一个宏，将特定范围的数据从多个excel文件传输到主表单。

I need to create a macro that will transfer specific ranges of data from multiple excel files into a master form.

更具体地说，答案在以下单元格中：C16：C35，C37：C45，C48：C49。一些答案可能是空白的。

To be more specific, the answers are in the following cells: C16:C35, C37:C45, C48:C49. Some of the answers may be blank.

我想将数据传输到主工作簿，从单元格A2开始转置数据文件名，然后答案从B2，C2 等开始。

I would like to transfer the data to a master workbook, transposing the data starting in cell A2 with the filename then the answers beginning in B2, C2 etc.

然后当我在第二个表格上运行宏时，（相同的单元格） ），我想转移到单元格A3（文件名），B3（答案）等的主工作簿。

Then when I run the macro on a second form, (same cells), I would like to transfer to the master workbook in cell A3(file name), B3 (answer), etc.

 你能帮我写一下吗？这个代码？

 Could you please help me write the code for this?

推荐答案

嗨John P5，

Hi John P5,

请尝试调整以下宏代码为了你的需要。

Please try and adjust below macro code for your need.

Sub MergeData()

    Dim tmpWb As Workbook

    Dim tmpWs As Worksheet

    Dim masterWs As Worksheet

    Dim lastRowIndex As Integer

    Set masterWs = ActiveSheet

     tmpFullpath = "C:\Users\v-guaxu\Desktop\Test\"

     Application.ScreenUpdating = False

     tmpFilename = Dir(tmpFullpath & "*.xlsx")

     Do While tmpFilename <> ""

    lastRowIndex = masterWs.Cells(masterWs.Rows.count, 1).End(xlUp).Row

     Set tmpWb = Application.Workbooks.Open(tmpFullpath & tmpFilename)

     Set tmpWs = tmpWb.Sheets(1)

     arr1 = WorksheetFunction.Transpose(Range("C16:c35"))

     arr2 = WorksheetFunction.Transpose(Range("C37:c45"))

     arr3 = WorksheetFunction.Transpose(Range("C48:c49"))

     columnIndex = 2

     masterWs.Cells(lastRowIndex + 1, 1) = tmpWb.Name

     For I = LBound(arr1) To UBound(arr1)

     masterWs.Cells(lastRowIndex + 1, columnIndex) = arr1(I)

     columnIndex = columnIndex + 1

     Next I

     

    For I = LBound(arr2) To UBound(arr2)

     masterWs.Cells(lastRowIndex + 1, columnIndex) = arr2(I)

     columnIndex = columnIndex + 1

     Next I

     For I = LBound(arr3) To UBound(arr3)

     masterWs.Cells(lastRowIndex + 1, columnIndex) = arr3(I)

     columnIndex = columnIndex + 1

     Next I

     tmpWb.Close False

    tmpFilename = Dir

    Loop

Application.ScreenUpdating = True

End Sub

结果

最好的问候，

Terry

这篇关于将多个Excel表单中的数据合并到主表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！