Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data (i.e. a single cell) from multiple spreadsheets intoa single sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data (i.e. a single cell) from multiple spreadsheets into a single sheet
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:"? .... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data (i.e. a single cell) from multiple spreadsheetsinto a single sheet
On Aug 4, 9:53*am, GS wrote:
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:"? ... -- 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data (i.e. a single cell) from multiple spreadsheetsinto a single sheet
On Aug 4, 10:11*am, James Sheriff wrote:
On Aug 4, 9:53*am, GS wrote: 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:"? ... -- 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.- 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidate data (i.e. a single cell) from multiple spreadsheets into a single sheet
on 8/4/2010, James Sheriff supposed :
On Aug 4, 9:53*am, GS wrote: 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:"? ... -- 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'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) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to consolidate multi-row data into a single row? | Excel Worksheet Functions | |||
How to consolidate multi-row data into a single row? | Excel Programming | |||
Consolidate multiple workbooks into a single worksheet | Excel Worksheet Functions | |||
Consolidate multiple spreadsheets into a single workbook | Excel Discussion (Misc queries) | |||
Multiple sheets as data for a single sheet | Excel Worksheet Functions |