Combining data from multiple workbooks into a single worksheet

My code is designed to allow the user to open multiple workbooks and take the data from each workbook copy it in to a new workbook and save that workbook in a designated location with a dynamic name.

My code is failing when the data that has been copied from the opened workbooks in to the new workbook.

Option Explicit
Option Base 1

Sub ConslidateWorkbooks()

Dim Filename As Variant, nw As Integer
Dim i As Integer, A() As Variant
Dim tWB As Workbook, aWB As Workbook, nWB As Workbook
Dim Sheet As Worksheet
Dim strFullname As String

Set tWB = ThisWorkbook
strFullname = "G:\CMG\DCM\Operations\Monthly Cycle\Monthly Transaction Upload\" & Range("PB") & "\" & Format(Range("CurrentDate"), "yyyy") & "\Raw Files\" & "Raw File - " & Range("PB") & Format(Range("CurrentDate"), "mmddyy") & ".csv"

Filename = Application.GetOpenFilename(FileFilter:="Excel Filter(*.csv), *.csv", Title:="Open File(s)", MultiSelect:=True)

'Application.ScreenUpdating = False

nw = UBound(Filename)
ReDim A(nw)
    For i = 1 To nw
        Workbooks.Open Filename(i)
        Set aWB = ActiveWorkbook
        A(i) = aWB.Sheets(1).Range("A6:L" & Cells(Rows.Count, 2).End(xlUp).Row)
        aWB.Close SaveChanges:=False

    Next i

Set nWB = Workbooks.Add
nWB.Sheets(1).Range("A1:L" & Cells(Rows.Count, 2).End(xlUp).Row) = WorksheetFunction.Transpose(A)
nWB.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True

'Application.ScreenUpdating = True

End Sub

I am expecting the data from each workbook (my test case is 4 separate workbooks, each with 1 sheet, all with a different number of rows but the exact number of columns (A-L)) to be copied in to a single sheet of a newly created workbook (consecutively copied).
I am receiving a

Run Time Error 13 Type Mismatch

on the following line of code:

nWB.Sheets(1).Range("A1:L" & Cells(Rows.Count, 2).End(xlUp).Row) = WorksheetFunction.Transpose(A)

Leave a Reply

Your email address will not be published. Required fields are marked *