none
Merge data from multiple spreadsheets into one master sheet. RRS feed

  • Question

  • Hi,

    I’m after some assistance creating the correct VBA to do the following:

    • There will be several workbooks stored in the same location (with different filenames but they should start with ‘Chase updates’) C:\Users\SmithJ\Documents\Spreadsheets
    • In these workbooks is a sheet called. ‘Chases’ all workbooks in the folder and the master sheet have the same format.
    • I want to copy the data from the chase spreadsheets the data I want to copy will start in Cell B14 and got across to cell I14 but the amount of rows of data will vary from sheet to sheet.
    • Once copied I want the data entered into the master spreadsheet ’Chase updates for WC’ on the ‘Totals’ sheet. Again the data would be pasted into the same location as it was pasted from B14 toI14 down, the data would need to merge all the data from the spreadsheets into this master sheet rather than copying over the data each time.

    I know there are similar solutions on the internet but I have not been abel to modify them to suit what I require.


    Monday, June 18, 2018 9:18 AM

All replies

  • Try the following. Correct the name of the master workbook if necessary, and please test on a copy of the master workbook.

    Sub CombineData()
        Dim wbkT As Workbook
        Dim wshT As Worksheet
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim strFolder As String
        Dim strFile As String
        Dim lngRow As Long
        Dim lngLastRow As Long
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Set wbkT = Workbooks("Chase updates for WC.xlsm")
        Set wshT = wbkT.Worksheets("Totals")
        lngRow = wshT.Range("B:I").Find(What:="*", _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        If lngRow < 14 Then lngRow = 14
        strFolder = "C:\Users\SmithJ\Documents\Spreadsheets\"
        strFile = Dir(strFolder & "Chase updates*.xls*")
        Do While strFile <> ""
            Set wbkS = Workbooks.Open(strFolder & strFile)
            Set wshS = wbkS.Worksheets("Chases")
            lngLastRow = wshS.Range("B:I").Find(What:="*", _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If lngLastRow >= 14 Then
                wshS.Range("B14:I" & lngLastRow).Copy Destination:=wshT.Range("B" & lngRow)
                lngRow = lngRow + lngLastRow - 13
            End If
            wbkS.Close SaveChanges:=False
            strFile = Dir
        Loop
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 18, 2018 9:34 AM
  • Hello, thank you for your help. 

    it thinks about it but doesn't do anything. this is going to sound stupid but is it because i'm trying to paste the data into a table? would it be easier to do it to a new sheet?

    Monday, June 18, 2018 6:39 PM
  • If the code doesn't do anything but doesn't display an error message either, it's probably because it doesn't find any workbooks whose name begins with 'Chase updates' in the folder 'C:\Users\SmithJ\Documents\Spreadsheets'. Make sure that the folder path is correct.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 18, 2018 8:08 PM
  • Hello, its this part it fails at returning an object variable not set. 

        lngRow = wshT.Range("B:I").Find(What:="*", _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    i double checked and the files are named correctly and are in the correct location. 

    i appreciate the help and time you have given me on this but managed to find another solution to the issue. sorry for wasting your time. 

    Tuesday, June 19, 2018 12:35 PM
  • I assume I don't have to provide modified code anymore...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 19, 2018 3:00 PM