Populating Master Spreadsheet from Excel files in folder with VBA

Calinora

New Member
Joined
Oct 9, 2018
Messages
13
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:

Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi & welcome to the board.
Cells is row, column so try this change
Code:
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?
 
Upvote 0
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?
 
Upvote 0
Insert this into the main sub as shown
Code:
    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.
 
Upvote 0
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.
 
Upvote 0
Can you provide your updated code.
 
Upvote 0
Code:
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
 
Upvote 0
Try
Code:
lRow = shTarget.Cells.Find("[COLOR=#ff0000]*[/COLOR]", [COLOR=#ff0000]lookIn:=xlValues[/COLOR], searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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