Help with VBA file merge

[gemw92]

Hi All,
I'm completely new to this,I literally hadn't even heard of VBA and had never looked at a macro before two days ago (but I'm learning)

I'm hoping someone can help with some code.

I have made a workbook already with VLOOKUP references on other sheets, and I want to write a macro that opens all the CSV files in a specific folder, and copies cells A1:K10 from these (even if some cells are empty) and places them a worksheet I have already created within my current workbook called RawData. I keep ending up that it is copying all the filled cells and ignoring the blanks, or it is opening the files in separate worksheets/a new work book.Please help! Much appreciated tia

 

[ParamRay]

.
.

Try something like the following. I've included many comments - please read through them first and make corresponding adjustments.

Sub filemerge()

    'for source (csv) files:
    Dim sfold As String
    Dim sname As String
    Dim spath As String
    Dim sbook As Workbook
    
    'for destination sheet:
    Dim dwkst As Worksheet
    Dim drnum As Long
    
    'set folder for source files
    'change as necessary...
    sfold = "C:\Users\gpeacock\Desktop\csv_files"
    
    'set destination sheet
    'change as necessary...
    Set dwkst = ActiveWorkbook.Worksheets("RawData")
    
    'get first source file name
    sname = Dir(sfold & Application.PathSeparator & "*.csv")
    
    'exit if no source files exist
    If sname = vbNullString Then Exit Sub
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'loop through source files and
    'copy A1:K10 to destination sheet...
    
    Do While sname <> vbNullString
        With dwkst
            drnum = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        End With
        spath = sfold & Application.PathSeparator & sname
        Set sbook = Workbooks.Open(Filename:=spath)
        sbook.Sheets(1).Range("A1:K10").Copy _
            Destination:=dwkst.Range("A" & drnum)
        sbook.Close SaveChanges:=False
        sname = Dir
    Loop
        
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    'notification that
    'macro is finished...
    MsgBox _
        Prompt:="Finished.", _
        Buttons:=vbInformation

End Sub

 

[gemw92]

Thank you very much for this! It seems to work fine, but it is still missing out the blank rows. (ie. on some sheets only a1:K5 may include data, but I need to copy all ten rows. Is there a way round this?

 

[ParamRay]

Thank you very much for this! It seems to work fine, but it is still missing out the blank rows. (ie. on some sheets only a1:K5 may include data, but I need to copy all ten rows. Is there a way round this?

Change to this:

Sub filemerge()

    'for source (csv) files:
    Dim sfold As String
    Dim sname As String
    Dim spath As String
    Dim sbook As Workbook
    
    'for destination sheet:
    Dim dwkst As Worksheet
    Dim drnum As Long
    
    'set folder for source files
    'change as necessary...
    sfold = "C:\Users\gpeacock\Desktop\csv_files"
    
    'set destination sheet
    'change as necessary...
    Set dwkst = ActiveWorkbook.Worksheets("RawData")
    
    'get first source file name
    sname = Dir(sfold & Application.PathSeparator & "*.csv")
    
    'exit if no source files exist
    If sname = vbNullString Then Exit Sub
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'loop through source files and
    'copy A1:K10 to destination sheet...
    
    drnum = 1
    Do While sname <> vbNullString
        spath = sfold & Application.PathSeparator & sname
        Set sbook = Workbooks.Open(Filename:=spath)
        sbook.Sheets(1).Range("A1:K10").Copy _
            Destination:=dwkst.Range("A" & drnum)
        sbook.Close SaveChanges:=False
        drnum = drnum + 10
        sname = Dir
    Loop
        
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    'notification that
    'macro is finished...
    MsgBox _
        Prompt:="Finished.", _
        Buttons:=vbInformation

End Sub

 

[gemw92]

Perfect, thank you so much!