Create a List from Same Cell on Multiple Sheets

  • Thread starter Thread starter Fritz Buchman
  • Start date Start date
F

Fritz Buchman

I am trying to create a list that comrised of the values of cell C3 on every
worksheet. Is there a simple way to do this? Thanks in advance.
 
Fritz


Try


Sub ddd()

Dim wS As Worksheet
Dim sTxt As String

For Each wS In Worksheets
sTxt$ = wS.Range("c3").Value
Debug.Print sTxt
Next

End Su
 
Thanks. I guess it will take code. I'm pretty familiar with VBA but not so
much with Excel's object model. Could you perhaps expand on the output
portion within the For-Next Loop? I'd like to the values in a vertical list
on a new sheet in the workbook.
 
Hi Fritz

No need to use VBA for this. Select cell C3 on the sheet that has the
data. Now right click on the Sheet name tab and click "Group all
sheets" now simply re-enter cell C3. Ungroup by selecting any other
sheet.

There is also Edit>Fill>Across Worksheets (once sheets are grouped) if
you wish to limit what is copied.

If this needs to be dynamic, you can use a simply reference forumla, or
this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells > 1 Then Exit Sub
If Target.Address = "$C$3" Then
Sheets.Select
Else
Me.Select
End If
End Sub

This MUST be placed in the Private Module of the Sheet Object. To get
there, right click on the Sheet name tab and select "View Code".

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
The following code will add a sheet, and list the values from cell C3 on
the existing worksheets.

'===============
Sub CreateList()
Dim i As Integer
Dim wsList As Worksheet
Dim ws As Worksheet
Set wsList = ActiveWorkbook.Sheets.Add

wsList.Name = "My List"
i = 1

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "My List" Then
wsList.Cells(i, 1).Value = _
ws.Range("C3").Value
i = i + 1
End If
Next ws

End Sub
'==================

Fritz said:
Thanks. I guess it will take code. I'm pretty familiar with VBA but not so
much with Excel's object model. Could you perhaps expand on the output
portion within the For-Next Loop? I'd like to the values in a vertical list
on a new sheet in the workbook.
Click to expand...
 
I guess my original message wasn't very clear. I have multiple sheets in my
workbook, each with a value in cell C3. On a new sheet, I'd like to have a
vertical list of all the values contained in cell C3 of the other
worksheets.
 
Fritz


Have you tried Debra's code yet?

Does it provide you with the data in the format you require?

The only other way I know of doing it is to have a number of formula'
on your list sheet that refers to c3 on a sheet

eg 10 sheets would require 10 formulas in rows 1 to 10

cell a1 would have foormula =Sheet2!$C$3
cell a2 would have foormula =Sheet3!$C$3
cell a3 would have foormula =Sheet4!$C$3
cell a4 would have foormula =Sheet5!$C$3
cell a5 would have foormula =Sheet6!$C$3
et
 
Thanks - worked like a charm. I appreciate your help with this.

Debra Dalgleish said:
The following code will add a sheet, and list the values from cell C3 on
the existing worksheets.

'===============
Sub CreateList()
Dim i As Integer
Dim wsList As Worksheet
Dim ws As Worksheet
Set wsList = ActiveWorkbook.Sheets.Add

wsList.Name = "My List"
i = 1

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "My List" Then
wsList.Cells(i, 1).Value = _
ws.Range("C3").Value
i = i + 1
End If
Next ws

End Sub
'==================
Click to expand...
 
Back
Top