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

 

[Fluff]

Hi & welcome to the board.
Cells is row, column so try this change

shTarget.Cells(lRow,2).PasteSpecial xlPasteValuesAndNumberFormats

Also you are currently setting the lrow to the last row of data that contains the text "18", is this correct?

 

[Calinora]

Thank you so much! it fixed the placement of the data. And no, you are absolutely right, it should look for empty cells. I have corrected that now.
It is inserting the rows in row 276 as the first one and it shouldn't do that they should be inserted in row 18 and then follow onwards from there. how do I fix this? It is also copying the entire range I have set. How do I get it to only copy the cells that contain data?

 

[Fluff]

Insert this into the main sub as shown

    Set shTarget = ThisWorkbook.Sheets("Master")
   [COLOR=#0000ff] shTarget.Range("18:" & Rows.Count).ClearContents[/COLOR]
    strPath = GetPath

When you say you only want cells with data, can you please explain what you mean.

 

[Calinora]

I have a spreadsheet that gets data manually input in Columns B-N from row 18 and down. But there might be 10 rows input in 1 sheet, and 6 in another sheet or maybe non at all. these are the ones I want to copy to my master. So if there is no input data in, lets say B18 I don't want it to copy it.

After inputting your code, it still inputs it in the bottom cells of the sheet.

 

[Fluff]

Can you provide your updated code.

 

[Calinora]

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("MIDP")
    shTarget.Range("18:" & Rows.Count).ClearContents
    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("TIDP")




            '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("", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1


    'Copy the data.
    shSource.Range(strRANGE_ADDRESS).Copy
    shTarget.Cells(lRow, 2).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

 

[Fluff]

Try

lRow = shTarget.Cells.Find("[COLOR=#ff0000]*[/COLOR]", [COLOR=#ff0000]lookIn:=xlValues[/COLOR], searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1

 

[Calinora]

That was absolutely brilliant! :D Thank you so much! It is working perfectly now.

 

[Fluff]

Glad to help & thanks for the feedback