Extract Specific Data From Many Worksheets

[msquared99]

Hello all,


I'm rather new to VBA (Excel 2010) which is why I'm asking this question.


I have a workbook that contains many worksheets within the workbook.

In column A of all the worksheets I am looking for "Total Wages & Salaries" aka TWS and "Total General & Administrative" aka TGA. Ideally I would like to sum TWS and TGA for each worksheet and have the result entered on a new sheet in the active workbook called "Summary".


Of the worksheets I am looking at, the data needed is in columns B, D,G and H. There are blank and hidden rows in the worksheets and they vary in the number of rows. Column B is PTD Actual, column D is PTD Budget, column G is YTD Actual and column H is YTD Budget.


So on the summary sheet column A would be the name of the worksheet, column B would be the sum of each PTD Actual total, column C would be the PTD Budget Total and so on.


The code below pulls in the name of each of the worksheets and a specific cell from each worksheet.

[pre]

Sub Extract()

Dim whst1 As Worksheet
Dim i As Long

i = 1

For Each wsht1 In ThisWorkbook.Worksheets
If LCase(wsht1.Name) <> LCase("Sheet1") Then
With Worksheets("Sheet1")
.Cells(i, "A").Value = wsht1.Name
.Cells(i, "B").Value = wsht1.Range("B2").Value
End With

i = i + 1
End If
Next

End Sub

I'm pretty sure I'll need something like this to get what I am after but do not know how to incorporate it into my code.

For Each ws In Worksheets
Set rFind = ws.Cells.Find(What:="Total Wages &Salaries", _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not rFind Is Nothing Then
ws.Select
rFind.Select
Exit Sub
End If
Next ws

[/pre]
Many thanks for your help!

 

[msquared99]

OK, finally I found some code that does most of what I want. So, how do I get it to extract the "Total G&A" part and add it to "Total Wages & Salaries"?

Here is the code:

[pre]

Sub Extract()

'   Object variables
Dim wks As Excel.Worksheet
Dim rCell As Excel.Range
Dim fFirst As String
'   {i} will act as our counter
Dim i As Long
'   You can use an input box to type in the search criteria
Dim MyVal As String
MyVal = "    Total Wages & Salaries"
'  Use this code to enter specific data MyVal = InputBox("What are you searching for?", "Search-Box","")
'   if we don't have anything entered, then exit the procedure
If MyVal = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'       Add a heading to the sheet with the specified search value
With Cells(1, 1)
.Value = "Found " & MyVal & " in the Link below:"
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With
i = 2
'       Begin looping:
'       We are checking all the Worksheets in the Workbook
For Each wks In ActiveWorkbook.Worksheets
If wks.Name <> "List" Then

'       We are checking all cells, we don't need the SpecialCells method
'       the Find method is fast enough
With wks.Range("A:A")
'           Using the find method is faster:
'           Here we are checking column "A" that only have {myVal} explicitly

Set rCell = .Find(MyVal, , , xlWhole, xlByColumns, xlNext, False)
'           If something is found, then we keep going
If Not rCell Is Nothing Then
'               Store the first address
fFirst = rCell.Address
Do
'                   Link to each cell with an occurence of {MyVal}
rCell.Hyperlinks.Add Cells(i, 1), "", "'" & wks.Name & "'!" & rCell.Address
wks.Range("B" & rCell.Row & ":I" & rCell.Row).Copy Destination:=Cells(i, 2)
Set rCell = .FindNext(rCell)
i = i + 1 'Increment our counter
Loop While Not rCell Is Nothing And rCell.Address <> fFirst
End If
End With
End If
Next wks
'   Explicitly clear memory
Set rCell = Nothing
'   If no matches were found, let the user know
If i = 2 Then
MsgBox "The value {" & MyVal & "} was not found on any sheet", 64, "No Matches"
Cells(1, 1).Value = ""
End If
'   Reset application settings
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[/pre]

 

[Luke M]

Does this really need to be a VB script, or would a formula work? I had a little trouble understanding your setup/problem, but if you're wanting a SUM/COUNT from different sheets and columns, why not use SUMIF or COUNTIF? Example of how to do this with the sheet name in col A.

=SUMIF(INDIRECT("'"&$A2&"'!A:A"),"TWG",INDIRECT("'"&$A2&"'!C"&COLUMN(B$1),FALSE))


Searched col A of specified worksheet for TWG and returns sum of corresponding values in col B of specified worksheets.

 

[msquared99]

Luke,


I do not need the sum of TWG for all sheets combined. Each sheet in the workbook represents a department, say Dept 100, Dept 200 and so on. So I have to add TWS and TWG for each department individually. This goes into a report so management can review each departments expenses.


Clearer?

 

[Luke M]

Yes...I thought that is what I said too? The formula would only be adding up values for a single sheet (referenced in col A). Since each sheet is a single department, this should work...unless there's something else I'm missing.