Need a "GetIF" function

C

Carpie

I have 10 separate spreadsheets spread across 10 different locations. They are all structured the same and I need to pull certain data from each one of them. Basically if column A equals "value", then I would like to have the whole row of information returned (to yet another spreadsheet that would be a summary spreadsheet for the info I need). Is there a function that would do this for me?
 
F

Frank Kabel

Hi
for a VBA solution see:
http://www.rondebruin.nl/copy5.htm

--
Regards
Frank Kabel
Frankfurt, Germany

Carpie said:
I have 10 separate spreadsheets spread across 10 different locations.
Click to expand...
They are all structured the same and I need to pull certain data from
each one of them. Basically if column A equals "value", then I would
like to have the whole row of information returned (to yet another
spreadsheet that would be a summary spreadsheet for the info I need).
Is there a function that would do this for me?
 
F

Frank Kabel

Hi
not tested but try the following (copies the entries starting from
sheet 2 to the first sheet):
Sub Copy_With_AutoFilter_2()
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim rng As Range
Dim Str As String Dim ws_index Set WS = Sheets("sheet1")
Set WS2 = Sheets("Sheet1")
Str = "Netherlands" for ws_index = 2 to
activeworkbook.worksheets.count Set WS = Sheets(ws_index)
WS.Range("YourRange").AutoFilter Field:=1, Criteria1:=Str
'Use a Dynamic range name,
http://www.contextures.com/xlNames01.html#Dynamic
'This example filter on the first column in the range (change the
field if needed) With WS.AutoFilter.Range
On Error Resume Next
' This will not copy the header row each time
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
_
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.Copy WS2.Range("A" &
LastRow(WS2) + 1)
End With
WS.AutoFilterMode = False next
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany

Carpie said:
That macro works fine but only for one sheet. I'm not a VBA guru by
Click to expand...
any stretch, so is there a way you can tell me what modifications would
be needed to have it grab from multiple sheets?
 
Top