Solved

Copy last row from multiple excel spread sheets into a summary spreadsheet

Posted on 2016-10-28
6
53 Views
Last Modified: 2016-10-30
Hello Experts,

I have a number of project excel spreadsheets, 1 per project, e.g. project1.xls, project2.xls, project3.xls, project4.xls to projectx.xls,

I want to create a project summary spreadsheet, e.g. projectsummary.xls, which needs to contain the last populated row of each of the individual spreadsheets - is this possible?

Many thanks

Jamie
0
Comment
Question by:Jamie
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 41864274
...is this possible?

Yes :)
0
 
LVL 33

Expert Comment

by:Norie
ID: 41864294
Where are the project files located?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 41864320
This seems to be working for me.

Option Explicit

Sub Consolidate()
    
    Dim Paths As Variant
    Dim xPath As Variant
    Dim WbName As String
    Dim SourceWb As Workbook
    Dim SourceWs As Worksheet
    Dim DestWb As Workbook
    Dim DestWs As Worksheet
    Dim WbWasOpen As Boolean
    Dim Counter As Long
    Dim LastR As Long
    
    Paths = Application.GetOpenFilename("Excel files, *.xls*", , "Select project files", , True)
    
    If IsArray(Paths) = False Then
        MsgBox "You didn't select any files, aborting", vbCritical, "Invalid Entry"
        Exit Sub
    End If
    
    Set DestWb = Workbooks.Add
    Set DestWs = DestWb.Worksheets(1)
    
    For Each xPath In Paths
        WbName = Mid(xPath, InStrRev(xPath, "\") + 1)
        On Error Resume Next
        Set SourceWb = Workbooks(WbName)
        If Err = 0 Then
            WbWasOpen = True
        Else
            WbWasOpen = False
            Set SourceWb = Workbooks.Open(xPath)
        End If
        On Error GoTo 0
        Set SourceWs = SourceWb.Worksheets(1)
        With SourceWs
            If Counter = 0 Then
                .Rows(1).Copy DestWs.[a1]
                Counter = 1
            End If
            LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
            If LastR > 1 Then
                Counter = Counter + 1
                .Rows(LastR).Copy DestWs.Cells(Counter, 1)
            End If
        End With
        If Not WbWasOpen Then SourceWb.Close False
    Next
    
    DestWs.Columns.AutoFit
    
    MsgBox "Done"
    
End Sub

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Jamie
ID: 41864930
Hi Patrick,

Apologies for delay in getting back to you, you came back amazingly fast with your great script - was not expecting an answer so quick!

Your script is 99% there, although the problem may be down to my test data files?

My test data is;

Project1. xls
A                  B
Update1-1      01/01/2016
Update1-2      01/02/2016
Update1-3      01/03/2016

Project2. xls
A                  B
Update2-1      02/01/2016
Update2-2      02/02/2016
Update2-3      02/03/2016

Project3. xls
A                  B
Update3-1      03/01/2016
Update3-2      03/02/2016
Update3-3      03/03/2016

When I run your script, the following is entered into;

ProjectSummary.xls
A                  B
Update1-1      01/01/2016            this row was not expected?
Update1-3      01/03/2016            correct
Update2-3      02/03/2016            correct
Update3-3      03/03/2016            correct

Any thoughts on why the 1st row is appearing?

Also, apologies, but thinking about it, rather than selecting the files, is it possible for ProjectSummary.xls to contain lookup data, which is automatically refreshed when the spreadsheet it loaded? e.g;

ProjectSummary.xls
A                              B                                          C
Project name 1            fullpathname/Project1. xls            Last row from Project1. xls      
Project name 2            fullpathname/Project1. xls            Last row from Project2. xls
Project name 3            fullpathname/Project1. xls            Last row from Project3. xls
,,,
Project name x            fullpathname/Projectx. xls            Last row from Projectx. xls


Many thanks

Regards

Jamie
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41865584
NO POINTS FOR THIS.

Patrick provided for a header row above the data which is why you are getting the unexpected row.

Delete rows 39 to 42 to get what you want.
1
 

Author Closing Comment

by:Jamie
ID: 41865676
Hi Patrick,

Fantastic script, exactly what I asked for, very much appreciated. Saqib Husain, Syed - many thanks for the suggested ameneded also :)

Best regards

Jamie
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question