consolidate data (i.e. a single cell) from multiple spreadsheets intoa single sheet

[James Sheriff]

Hello. I have about 900 worksheets (worksheet1, worksheet2, etc) all
of which have a single cell (mixed in with many other cells with data)
that i am interesting in copying and pasting into a single
spreadsheet. Am wondering if this is possible to pull of.

Basically, in every single worksheet, in rows 2, 3 or 4, there is a
single cell that has "PCP:" followed by the address i need.

Ideally, i can find a way to copy the contents of all of those address
cells into a single worksheet in A2, A3, A4, etc (i.e., into a single
column).

Any suggestions (including VBA code) would be greatly appreciated.

 

[GS]

James Sheriff formulated on Wednesday :

Hello. I have about 900 worksheets (worksheet1, worksheet2, etc) all
of which have a single cell (mixed in with many other cells with data)
that i am interesting in copying and pasting into a single
spreadsheet. Am wondering if this is possible to pull of.

Basically, in every single worksheet, in rows 2, 3 or 4, there is a
single cell that has "PCP:" followed by the address i need.

Ideally, i can find a way to copy the contents of all of those address
cells into a single worksheet in A2, A3, A4, etc (i.e., into a single
column).

Any suggestions (including VBA code) would be greatly appreciated.

More info would be helpful for making suggestions.

Are all 900 worksheets in a single workbook?
Are all the cells containing the address you need always in the same
column?
Do you need to parse the address from the cell contents?
Are there likely to be several cells with needed addresses on one
sheet? IOW, do you want to extract addresses from all cells on a sheet
in rows 2 to 4 that contain "PCP:"?
....

 

[James Sheriff]

James Sheriff formulated on Wednesday :





More info would be helpful for making suggestions.

  Are all 900 worksheets in a single workbook?
  Are all the cells containing the address you need always in the same
column?
  Do you need to parse the address from the cell contents?
  Are there likely to be several cells with needed addresses on one
sheet? IOW, do you want to extract addresses from all cells on a sheet
in rows 2 to 4 that contain "PCP:"?
...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for the response. All cells are in column A. (sorry, should
have pointed that out). No need to parse the address. Only one Address
in one sheet. So the resulting output could be:

In A2: PCP....with address (which comes from sheet1).
In A3: PCP....with address (which comes from sheet2).
In A4: PCP....with address (which comes from sheet3).
Etc.

 

[Don Guillett Excel MVP]

Thanks for the response. All cells are in column A. (sorry, should
have pointed that out). No need to parse the address. Only one Address
in one sheet. So the resulting output could be:

In A2: PCP....with address (which comes from sheet1).
In A3: PCP....with address (which comes from sheet2).
In A4: PCP....with address (which comes from sheet3).
Etc.- Hide quoted text -

- Show quoted text -

Option Explicit
Sub FindemSAS()
Dim ms As String
Dim mr
Dim i As Long
ms = ActiveSheet.Name
For i = 1 To Worksheets.Count
With Sheets(i)
If .Name <> ms Then
Set mr = .Columns("A").Find(What:="pcp", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not mr Is Nothing Then
' MsgBox .Name & " " & mr.Row
Cells(i, 1) = .Name
Cells(i, 2) = .Cells(mr.Row, 2)
End If
End If
End With
Next i
End Sub

 

[GS]

Thanks for the response. All cells are in column A. (sorry, should
have pointed that out). No need to parse the address. Only one Address
in one sheet. So the resulting output could be:

In A2: PCP....with address (which comes from sheet1).
In A3: PCP....with address (which comes from sheet2).
In A4: PCP....with address (which comes from sheet3).
Etc.

Don's solution is pretty much what I'd do. Though, I see he's putting
the sheetname in ColumnA and the address in ColumnB; ..which is not
what you asked for! In this case, just comment out the line that enters
sheetname and change the Column arg in the next line to 1 instead of 2.
(-OR- you can delete everything from the right of the first = sign to
the right of the 2nd = sign)