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.Activate nWB.Sheets(1).Range("A1:L" & Cells(Rows.Count, 2).End(xlUp).Row) = WorksheetFunction.Transpose(A) nWB.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True nWB.Close '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)