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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
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?
 

Calinora

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
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.
 

Calinora

New Member
Joined
Oct 9, 2018
Messages
13

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
Can you provide your updated code.
 

Calinora

New Member
Joined
Oct 9, 2018
Messages
13

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
lRow = shTarget.Cells.Find("[COLOR=#ff0000]*[/COLOR]", [COLOR=#ff0000]lookIn:=xlValues[/COLOR], searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
 

Calinora

New Member
Joined
Oct 9, 2018
Messages
13
That was absolutely brilliant! :D Thank you so much! It is working perfectly now.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,006
Messages
5,526,244
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top