Pulling specific data from multiple workbooks into one

[Aggie2014]

Hi all,

I have been reading many of the posts on this board that have helped me immensely in learning VBA, however the macro that I’m working to create now hasn’t had a discussion that I’ve been able to find.

I have about 15 files with different financial data in a folder; each file has different amount of rows but they all have several Rows with category names and totals. For example “total revenues” and “Total capital outlay” that are located in column C with the totals following in Columns D-I.

what I’m looking for is a macro that will copy those rows with totals from each workbook into a new summary workbook.

complicating matters further, each file contains a unique name in cell A3 that I would like to show in the summary workbook to identify which file the totals came from.

apologies for the rambling but I wanted to describe the conundrum in the most detail possible. Thanks all!

 

[jolivanes]

How many sheets in each workbook (file) do you need to check.
Multiple sheets or just one?
If multiple sheets, do all sheets need to be checked?
If just one, what is it's name or where is it located (2nd sheet or 1st sheet ?) Or does each workbook just have one sheet?
Do you just want to copy the 7 cells (C - I)? Or the whole Row?
Sounds like there will be multiple times "total revenues" and 'Total capital outlay" in each sheet in Column C. True or did I misunderstand?
Where does the unique name from Cell A3 need to be pasted. Before the data (Like Column A and data pasted starting at Column B) or at the end of the data?

 

[Aggie2014]

Each workbook has only one sheet in it. I would prefer to copy the entire row because there may be data that would be added later to other columns; however C-I is what needs to be copied now.

Each sheet has the category totals show up only once, and do not repeat within the individual files; so each sheet will have "total revenues" and other categories show up only once.

The unique name from A3 would need to be posted before the data in column A with the data beginning to be populated in column B

Thanks for your help!

 

[jolivanes]

Try this.

Sub Copy_From_All_Workbooks()
    Dim wb As String, i As Long, vals, colcnt As Long
    Dim Found As Range
    Dim Dest As Range
    Application.ScreenUpdating = False
    vals = Array("total revenues", "Total capital outlay")    '<---- Make sure the spelling is proper
    Set Dest = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            For i = LBound(vals) To UBound(vals)
                Set Found = Workbooks(wb).Sheets(1).Columns(3).Find(vals(i), , , 1)
                If Not Found Is Nothing Then
            colcnt = Workbooks(wb).Sheets(1).UsedRange.Columns.Count    
                    Dest.Value = Workbooks(wb).Name
                    Dest.Offset(, 1).Resize(, colcnt).Value = Found.Offset(, -2).Resize(, colcnt).Value
                    Set Dest = Dest.Offset(1)
                End If
            Next i
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[jolivanes]

Forgot to mention to have all relevant workbooks in the same folder, including the "Master" with the code in it.

 

[Aggie2014]

Thanks so much!

When I enter the code into the module, I get a Compile Error Expected: Expression for the portion below

vals = Array("***TOTAL REVENUE***", "***TOTAL PERSONNEL SERVICES***", "***TOTAL SUPPLIES***", "***TOTAL SERVICES/CHARGES***", "***TOTAL REIMBURSEMENTS***", "***TOTAL CAPITAL OUTLAY***")

Would it have something to do with the dim statement above?

Dim wb As String, i As Long, vals, colcnt As Long

I'm not sure if vals was declared as Long along with colcnt or if not.

Thanks!

 

[jolivanes]

It's probably the multiple asterisks.
Have to look into that unless someone with more knowledge about that comes along first.

 

[Aggie2014]

I removed the asterisks from the code to see if that would resolve the issue and got a different compile error message: Expected List Separator or ). I looked through the listing in the array and they're separated by commas so I'm not sure where the issue is. The line of code that is highlighted in red is as follows:

vals = Array("TOTAL REVENUE", "TOTAL PERSONNEL SERVICES", "TOTAL SUPPLIES", "TOTAL SERVICES/CHARGES", "TOTAL REIMBURSEMENTS", "TOTAL CAPITAL OUTLAY")

 

[jolivanes]

But the asterisks are still in the sheets so it will not find anything.

 

[Aggie2014]

I didn't run the macro yet, I got the error after I entered the code into the editor prior to running it.