Can't get the script to consolidate data side-by-side

Ares_Hera

New Member
Joined
Mar 31, 2018
Messages
1
Hi everyone,
I'd really appreciate if someone helped me with this problem. Admittedly, I'm very new to all this so please go easy on me.
I have a code that consolidates data from a large number of workbooks to a single master worksheet. I'm targeting same cells from different workbooks (for example A5 from all workbooks and pasting them into my worksheet as a list under A1, and A13 under B1, etc). But when it completes the A1 column, it goes to the next column AND the next row. (e.g. when A1 to A11 is finished, it starts pasting to B12 in the destination, rather than starting from B1).
Here is the code:

Code:
Sub MergeFiles()
    Dim path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer

    RowofCopySheet = 1 'Row to start on in the sheets you are copying from

    ThisWB = ActiveWorkbook.Name

    path = "C:\batch"

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set shtDest = ActiveWorkbook.Sheets(1)
    Filename = Dir(path & "\*.csv", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
            Set CopyRng = Wkb.Sheets(1).Range("A5:E5")
            Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
            CopyRng.Copy Dest
            Wkb.Close False
        End If

        Filename = Dir()
    Loop

    Range("A1").Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    MsgBox "Done!"
End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi & welcome to MrExcel.
Your code is is copying to col A regardless of how many rows are used. So I don't understand what your problem is. Could you please re-explain?
 
Upvote 0

Forum statistics

Threads
1,223,999
Messages
6,175,891
Members
452,680
Latest member
Kikaiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top