Categories

# 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

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)
``````
{"ft_lang_label":"__label__en","ft_lang_prob":0.72202235,"math_prob":0.88736314,"size":1861,"snap":"2020-45-2020-50","text_gpt3_token_len":524,"char_repetition_ratio":0.12062466,"word_repetition_ratio":0.023622047,"special_character_ratio":0.25416443,"punctuation_ratio":0.16620499,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9704471,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-10-31T16:16:38Z\",\"WARC-Record-ID\":\"<urn:uuid:e5808777-0208-45da-8b4d-9dfdf6eb5e1a>\",\"Content-Length\":\"104253\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:c6ecf9a0-115e-4899-8b07-516f6f65092b>\",\"WARC-Concurrent-To\":\"<urn:uuid:77706594-83d6-421c-8969-82f789f5c499>\",\"WARC-IP-Address\":\"172.67.144.135\",\"WARC-Target-URI\":\"https://www.onooks.com/combining-data-from-multiple-workbooks-into-a-single-worksheet/\",\"WARC-Payload-Digest\":\"sha1:MOB3SLSQWNVX5N42NH4ZELLNFWPWQAKZ\",\"WARC-Block-Digest\":\"sha1:3H7IXSRCCHBUYC32WJRDIEAVD7ASDBQZ\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-45/CC-MAIN-2020-45_segments_1603107919459.92_warc_CC-MAIN-20201031151830-20201031181830-00079.warc.gz\"}"}