将多个excel数据复制到一个excel中

Question

我有多个 CSV 文件数据，我需要根据单元格值将所有 CSV 数据复制到一个 excel 中，例如源 excel sheet1 B2:C2 直到最后行数据，我需要复制目标 Excel Sheet3 列 B2:C2 直到最后，这样将多个工作簿复制到一个工作簿中

 Sub Copy_Paste()
  Dim Openfile As String ' Openfile
  Dim lstrow As Long, lCol As Long
  Dim i As Long, j As Long
  Dim rng As Range
  Dim temp As Excel.Workbook
  Dim temp1 As Excel.Workbook


    lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    lCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End("AI").Column
    Application.ScreenUpdating = False
    For i = 2 To lstrow
     For j = B To lCol

      'Open The Workbook FME CSV File
             Set temp1 = Workbooks.Open(ws.Cells("2" & j).Value)



                        ThisWorkbook.Sheets ("Main")
                              'Activate the destination worksheet
                               Set temp = Workbooks.Open(Range("H" & i).Value)
                               Sheets.Range("E" & i).Activate
                                'copy the data
                                 temp1.Range("B:C").Copy 'need to copy the data B2&C2 to till End column
                               'Select the target range
                              ThisWorkbook.Range("L").Select
                              'Paste in the target destination
                              temp.Range("B2:C2").PasteSpecial xlPasteValues

                  Application.CutCopyMode = False
                    temp1.Save
                    temp1.Close
                     Next j ' Once Column Cell empty goto next row
                     Next i
             End Sub