Populating Master Spreadsheet from Excel files in folder with VBA

[Calinora]

Hello,

I have a mastersheet that needs to pull data from about 10 spreadsheets and insert the rows underneath each other. However, it is not working out as I think it should. It is running the code, but it is pasting the results on top of each other and starting from column S instead of column B.

the code is as following:

Sub CopyDataBetweenWorkbooks()


    Dim wbSource As Workbook
    Dim shTarget As Worksheet
    Dim shSource As Worksheet
    Dim strFilePath As String
    Dim strPath As String


    ' Initialize some variables and
    ' get the folder path that has the files
    Set shTarget = ThisWorkbook.Sheets("Master")
    strPath = GetPath


    If Not strPath = vbNullString Then


        ' Get all the files from the folder
        strfile = Dir$(strPath & "*.xlsm", vbNormal)


        Do While Not strfile = vbNullString


            ' Open the file and get the source sheet
            Set wbSource = Workbooks.Open(strPath & strfile)
            Set shSource = wbSource.Sheets("Data")




            'Copy the data
            Call CopyData(shSource, shTarget)


            'Close the workbook and move to the next file.
            wbSource.Save
            wbSource.Close False
            strfile = Dir$()
        Loop
    End If


End Sub


' Procedure to copy the data.
Sub CopyData(ByRef shSource As Worksheet, shTarget As Worksheet)


    Const strRANGE_ADDRESS As String = "B18:N32"


    Dim lRow As Long


    'Determine the last Row.
     lRow = shTarget.Cells.Find("18", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1


    'Copy the data.
    shSource.Range(strRANGE_ADDRESS).Copy
    shTarget.Cells(18, lRow).PasteSpecial xlPasteValuesAndNumberFormats


    ' Reset the clipboard.
    Application.CutCopyMode = xlCopy


End Sub




' Fucntion to get the folder path
Function GetPath() As String


    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select a folder"
        .Title = "Folder Picker"
        .AllowMultiSelect = False


        'Get the folder if the user does not hot cancel
        If .Show Then GetPath = .SelectedItems(1) & "\"


    End With


End Function

I hope someone can help me with what i'm doing wrong.

Thanks,

/Calinora

 

[Calinora]

Just a quick one -

Can I make this:

  shTarget.Range("18:" & Rows.Count).ClearContents

Only Clear the rows from column B till column N?

 

[Fluff]

Try

 shTarget.Range("B18:N" & Rows.Count).ClearContents

 

[Calinora]

Thank you! Works perfectly!

 

[Fluff]

You're welcome