dcsimg
Results 1 to 20 of 20

Thread: Copy contents from individual spreadsheets to one master spreadsheet

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Copy contents from individual spreadsheets to one master spreadsheet

    Hi...I have a requirement where there may be 50-100 individual spreadsheets containing data which needs to be copied over to the appropriate worksheets on a master spreadsheet. All the individual spreadsheets can be saved in one folder in the same directory as the master spreadsheet.
    Ideally I would like to automate this process so when the master spreadsheet is opened it us updated with all the data from the individual spreadsheets without having to run the code for each individual spreadsheet. To avoid duplication once the individual spreadsheet content has been copied over to the master spreadsheet the individual spreadsheet should move to say a archive folder on the same directory.
    The rows on the worksheets on the master spreadsheets would need to keep expanding as more data is added.

    I don't have a clue with where to start with this as it's too complicated for me and would really appreciate some help from the experts please.

    I am attaching a template 1 file which will be the individual spreadsheets and the master spreadsheet where the data needs to be copied to.

    Thanks

    Master List - Sample.zipTemplate 1.zip

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    so the data in the template sheet has to be split into the master list sheets?
    are all the worksheets in the folder to be imported into the master?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Hi yes all the data in the templates which will have two worksheets needs to split into the one master workbook worksheets depending on the type Accrual, Deferred, etc.
    To make it easier we'll save all the individual workbooks in one folder, the same folder as the master workbook then once the individual workbook contents have been copied across I would like the template workbook to move to Archive folder so it doesn't get copied across again unless if there's another way to prevent it being duplicated.
    Thanks

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    unless if there's another way to prevent it being duplicated.
    there would be other ways, but that would be the simplest
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Quote Originally Posted by westconn1 View Post
    there would be other ways, but that would be the simplest
    That's fine. I just need the simplest solution please so I can understand it as well. Thanks.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Quote Originally Posted by westconn1 View Post
    there would be other ways, but that would be the simplest
    That's fine. I just need the simplest solution please so I can understand it as well. Thanks.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Hi...Pete can you help with this please? Thanks

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    i have been looking at it (and still am), but it is quite a big ask and i had no time at the weekend

    is the x in A6 normal? or just in this sample workbook?
    Last edited by westconn1; Mar 26th, 2019 at 03:59 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    you can have a play with this, i have tested it against the one sample workbook

    Code:
    Dim cn As Connection, rs As Recordset
    Dim intext, sh, typ      ' all variants
    Dim col As Integer, rw As Integer, x As Integer, i As Integer, j As Integer, k As Integer
    Dim mypath As String, fname As String, tmp As String
    Dim sht As Worrksheet, nxtrw As Range
    Set cn = New Connection
    Set rs = New Recordset
    Set cat = New Catalog
    sh = Array("INCOME", "EXPENDITURE")
    intext = Array("INTERNAL", "EXTERNAL")
    typ = Array("ACCRUAL", "PREPAID", "DEFERRED ")
    mypath = ThisWorkbook.Path & Application.PathSeparator
    fname = Dir(mypath & "*.xlsx")
    Do While Len(fname) > 0
        
        If Not fname = Replace(ThisWorkbook.Name, ".xlsm", ".xlsx") Then
            With cn
                .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & fname & ";" & _
                "Extended properties='Excel 12.0; header  = no'"
                .Open
            End With
            For i = 0 To UBound(sh)
                For j = 0 To UBound(typ)
                    For k = 0 To UBound(intext)
                        Sql = "select * from [" & sh(i) & "$] where f3 = '" & typ(j) & "' and f2 = '" & intext(k) & "';"
                        rs.Open Sql, cn, adOpenStatic, adLockReadOnly
                        Debug.Print rs.RecordCount & vbTab & Sql & vbTab
                        If Not rs.BOF Then
                            For Each sht In ThisWorkbook.Sheets
                                tmp = sh(i)
                                If tmp = "EXPENDITURE" Then tmp = "CREDITORS"
                                If InStr(sht.Name, tmp) > 0 Then
                                    If InStr(sht.Name, typ(j)) > 0 And InStr(sht.Name, intext(k)) > 0 Then Exit For
                                End If
                                
                            Next
                            If IsEmpty(sht) Then MsgBox "No sheet " & tmp & " " & intext(k) & " " & typ(j)
                            Debug.Print sht.Name & vbTab & rs.RecordCount
                            Set nxtrw = sht.Cells(Rows.Count, 3).End(xlUp).Offset(1)
                            x = 0
                            rw = 0
                            Do Until rs.EOF
                                For col = 3 To 11
                                    If tmp = "CREDITORS" And col = 7 Then x = 1
                                    nxtrw.Offset(rw, col - 3) = rs.Fields(col + x)
                                    
                                Next
                                rs.MoveNext
                                x = 0
                                rw = rw + 1
                            Loop
                            
    '                    insert data here
                            
                        End If
                        rs.Close
                    Next
                Next
            Next
            cn.Close
            Name mypath & fname As mypath & "archive\" & fname   ' the archive folder must already exist
        End If
        
        fname = Dir
    Loop
    you will probably find a few undeclared variables,
    you will need a reference to ADO, i was using 2.8, but you can try a later version if you want

    to make the coding slightly easier, i changed the names of the worksheets containing prepay to prepaid and there is an issue in the spelling of deferred in the sheet names and the sample workbook, i fixed the worksheet data, so all were spelled correctly
    if any of the input workbooks are open then the name statement will fail to move the .xlsx file, with error, but the rest of the code will still run

    i am sure there is likely to still be some issues, so do plenty of testing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Hi Pete thanks for the code.

    The X and dots in the other rows in column A is normal.

    Just wanted to ask how difficult it would be if on the Master workbook we kept it to say prepay rather than change it to prepaid?
    Can you please share the sample workbooks you tried the code on as I'm getting compile error on the DIM when I try to run the code using the sample workbooks I attached (I corrected the deferred spelling)?
    Thanks so much for your help.
    Last edited by fusion001; Mar 27th, 2019 at 05:21 PM.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Just wanted to ask how difficult it would be if on the Master workbook we kept it to say prepay rather than change it to prepaid?
    just about anything is possible, alternatively can the data be changed to prepay? it is just easier to have matching strings

    i will add the workbook here later as it is on a different computer
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Hi Pete...for some reason I can only access vbforum during certain times in the day no matter which browser i use.

    There could potentially be 50/100 templates for each master workbook. Each area will have their own master workbook and templates though these will be the same but I guess I could add the word "Prepaid" at the front of the worksheet tab name if that will make things easier.

    I've noticed in the code above you've written "insert data here" what needs to go here?

    Code:
                       rw = rw + 1
                            Loop
                            
    '                    insert data here
                            
                        End If
                        rs.Close
    Thanks again for your help with this.

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    I've noticed in the code above you've written "insert data here" what needs to go here?
    probably nothing, i just put that in as a placeholder till i got the code to work to that stage, then i put code to copy the data to worksheet without removing the comment

    but I guess I could add the word "Prepaid"
    instead, to use the sheet names as is, try editing this line, like
    Code:
    If InStr(sht.Name, replace(typ(j), "PREPAID", "Prepay")) > 0 And InStr(sht.Name, intext(k)) > 0 Then Exit For
    unlike some other parts of the code this is case sensitive, so make sure it matches case where required

    this solution seems really simple now, but was creating an issue when i was trying to put it together, i just typed that in the browser, so may contain errors

    for some reason I can only access vbforum during certain times in the day
    so can i, but that would be because i am at work during the day
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Thanks again Pete. I'd really appreciate it if you can share the workbook you tried this code with. Thanks

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    here it is, i hope it saved any editsMaster List - Sample.zip
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Thanks Pete. The code works fine however we now have a revised master workbook where there are a few more additional tabs for example one tab says "Creditor Notes" another says "Internal Creditors" so now the code runs without errors but none of the worksheets are populated. Could it be because of the new worksheets the code doesn't know which sheets to add the data to maybe this bit of the code needs changing?
    Code:
     If tmp = "EXPENDITURE" Then tmp = "CREDITORS"
    The same thing applies for the Income worksheets.

    All the other worksheets are still in the master workbook like the Internal Accrual Creditors etc.

    Thanks

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    as you have updated the tabs in the master within such a short time, i would expect ongoing updates
    in this case you would require dynamic code to work for any new additional tabs

    i tried to do that by using arrays, you can add additional strings to any of the arrays (see the 3 arrays near the top of the code), but the tabs and data need to match the pattern of data to tab names, otherwise you would just have to do each data and tab as individual codes

    the snippet you posted above was where the data did not match the name on the tab, but mostly the data on each tab matched the data in each workbook

    i don't know that i can help you much more, but will endeavour to answer specific questions to help you update the code as required
    for this type of work you hire an inhouse programmer
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Hi Pete it was unfortunate that some extra tabs had to be added but I don't expect any more tabs to be added now.
    I don't have a clue how the arrays work or what function they are carrying out in this code so no idea how to add more arrays to get the code to work dynamically. Hopefully one day as my knowledge develops I'll understand things better.
    Thanks for your help with this.

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    303

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    Hi Pete it was unfortunate that some extra tabs had to be added but I don't expect any more tabs to be added now.
    I don't have a clue how the arrays work or what function they are carrying out in this code so no idea how to add more arrays to get the code to work dynamically. Hopefully one day as my knowledge develops I'll understand things better.
    Thanks for your help with this.

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,245

    Re: Copy contents from individual spreadsheets to one master spreadsheet

    when i worked from your original workbook, i found that the values to be taken from each workbook was to be put into sheets that included all the same values in the sheet names, with some exceptions, which were deferred (owing to a spell error, that i believe you fixed), prepaid versus prepay, and creditors versus expenditure
    this made it possible to add the searched values to arrays, then just perform looping to cover all possibilities on the sheets listed in that array, any invalid combination (or valid combination with no data) would return no records, so would be ignored. combinations that returned results are then added to the bottom of the appropriate worksheet in the master

    Code:
    sh = Array("INCOME", "EXPENDITURE")       ' sheets from importing workbook
    intext = Array("INTERNAL", "EXTERNAL")                ' obviously internal or external
    typ = Array("ACCRUAL", "PREPAID", "DEFERRED")      ' type of transaction
    these are the 3 arrays, you should be able to see that thes match (with the exceptions listed above) that they match both the data results to be returned from each workbook and also match the content of the sheet names

    any additional tabs that would follow similar pattern could then just be added into the 3 arrays

    as there was no sample data for additional tabs, i can only guess if they might follow the same pattern (or could be made to do so)

    you should not need more arrays, just add extra sheets to be imported and data to be returned to the existing arrays, then determine if the data to be imported will align with the correct columns in the worksheet in the master
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width