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?

get value from same cell on all sheets in a workbook.
 

Hi Gene,

maybe indirect function?

Regards,
Ivan

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

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?

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?

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?

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?