Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
I am trying to extract value from a particular cell, A4 from every sheet
(which are all name slightly differently) and tabulate on a summary sheet (eg. "Sheet1"). Does anyone know how to do this efficiently? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
Hi Gene,
maybe indirect function? Regards, Ivan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
Hi Gene
The following code will extract the data from A$ of every sheet and and transfers it to a sheet named summary with the sheet name. Hope this is what you were looking for Private Sub CommandButton1_Click() Dim ws As Worksheet i = 1 Sheets.Add ActiveSheet.Name = "Summary" For Each ws In Worksheets Worksheets(ws.Name).Select Worksheets(ws.Name).Range("a4").Select Worksheets(ws.Name).Range("a4").Copy Destination:=Worksheets("Summary").Cells(i, 2) Worksheets("Summary").Cells(i, 1) = ws.Name i = i + 1 Next ws End Sub Cheers Christian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
Gene
Not knowing any more than what you say, there are two ways to do what you want. One way is to put formulas in the cells on the Summary sheet. That way those cells will always display the contents of the A4 cells. Such a formula would look like the following. Each cell in the summary sheet would have a formula for its particular sheet.: =SheetName!F6 Another way is to use a macro such as what is shown below. I assumed you have some sheets that you don't want included. I also assumed that every sheet you want included is named AbleXXX where "Able" is constant and "XXX" changes . Also the summary sheet is named "Summary" and the list starts in A1 of the Summary sheet. HTH Otto Sub CopyData() Dim Dest As Range Dim ws As Worksheet Set Dest = [A1] For Each ws In ActiveWorkbook.Worksheets If Left(ws.Name, 4) < "Able" Then GoTo NextSht Dest.Value = ws.Range("F6").Value Set Dest = Dest.Offset(1) NextSht: Next ws End Sub "GeneWan" wrote in message ... I am trying to extract value from a particular cell, A4 from every sheet (which are all name slightly differently) and tabulate on a summary sheet (eg. "Sheet1"). Does anyone know how to do this efficiently? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
Hi Otto,
This looks very prospective, am gonna try this out shortly, thank you very much! "Otto Moehrbach" wrote: Gene Not knowing any more than what you say, there are two ways to do what you want. One way is to put formulas in the cells on the Summary sheet. That way those cells will always display the contents of the A4 cells. Such a formula would look like the following. Each cell in the summary sheet would have a formula for its particular sheet.: =SheetName!F6 Another way is to use a macro such as what is shown below. I assumed you have some sheets that you don't want included. I also assumed that every sheet you want included is named AbleXXX where "Able" is constant and "XXX" changes . Also the summary sheet is named "Summary" and the list starts in A1 of the Summary sheet. HTH Otto Sub CopyData() Dim Dest As Range Dim ws As Worksheet Set Dest = [A1] For Each ws In ActiveWorkbook.Worksheets If Left(ws.Name, 4) < "Able" Then GoTo NextSht Dest.Value = ws.Range("F6").Value Set Dest = Dest.Offset(1) NextSht: Next ws End Sub "GeneWan" wrote in message ... I am trying to extract value from a particular cell, A4 from every sheet (which are all name slightly differently) and tabulate on a summary sheet (eg. "Sheet1"). Does anyone know how to do this efficiently? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
Obviously change the F6 to A4. Otto
"Otto Moehrbach" wrote in message ... Gene Not knowing any more than what you say, there are two ways to do what you want. One way is to put formulas in the cells on the Summary sheet. That way those cells will always display the contents of the A4 cells. Such a formula would look like the following. Each cell in the summary sheet would have a formula for its particular sheet.: =SheetName!F6 Another way is to use a macro such as what is shown below. I assumed you have some sheets that you don't want included. I also assumed that every sheet you want included is named AbleXXX where "Able" is constant and "XXX" changes . Also the summary sheet is named "Summary" and the list starts in A1 of the Summary sheet. HTH Otto Sub CopyData() Dim Dest As Range Dim ws As Worksheet Set Dest = [A1] For Each ws In ActiveWorkbook.Worksheets If Left(ws.Name, 4) < "Able" Then GoTo NextSht Dest.Value = ws.Range("F6").Value Set Dest = Dest.Offset(1) NextSht: Next ws End Sub "GeneWan" wrote in message ... I am trying to extract value from a particular cell, A4 from every sheet (which are all name slightly differently) and tabulate on a summary sheet (eg. "Sheet1"). Does anyone know how to do this efficiently? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
get value from same cell on all sheets in a workbook.
I've changed it further to the cell I was going for, it works perfect!
Thanks again! :) "Otto Moehrbach" wrote: Obviously change the F6 to A4. Otto "Otto Moehrbach" wrote in message ... Gene Not knowing any more than what you say, there are two ways to do what you want. One way is to put formulas in the cells on the Summary sheet. That way those cells will always display the contents of the A4 cells. Such a formula would look like the following. Each cell in the summary sheet would have a formula for its particular sheet.: =SheetName!F6 Another way is to use a macro such as what is shown below. I assumed you have some sheets that you don't want included. I also assumed that every sheet you want included is named AbleXXX where "Able" is constant and "XXX" changes . Also the summary sheet is named "Summary" and the list starts in A1 of the Summary sheet. HTH Otto Sub CopyData() Dim Dest As Range Dim ws As Worksheet Set Dest = [A1] For Each ws In ActiveWorkbook.Worksheets If Left(ws.Name, 4) < "Able" Then GoTo NextSht Dest.Value = ws.Range("F6").Value Set Dest = Dest.Offset(1) NextSht: Next ws End Sub "GeneWan" wrote in message ... I am trying to extract value from a particular cell, A4 from every sheet (which are all name slightly differently) and tabulate on a summary sheet (eg. "Sheet1"). Does anyone know how to do this efficiently? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Select the same cell in different sheets in the same workbook? | Excel Programming |