Create a list of text from a single cell on several worksheets?

D

deidre

I am trying to create a list (like summary) of all the comments from a
survey. The comment are all in the same cell (i.e. C70), but on approximatley
140 worksheets.
I would like them all to be listed wihtin my 'summary' worksheet... but
can't figure out how to 'compile' them. Help please.
 
D

Don Guillett

try something like

for each ws in worksheets
dlr=sheets("summary").cells(rows.count,"a").end(xlup).row+1
sheets("summary").cells(dlr,"a").value=ws.range("c70")
next ws
 
C

CLR

Put this formula in A1 and copy down for your number of sheets........

="=Sheet"&ROW(A1)+1&"!$C$70"

Then, run this little macro........

Sub TextToFormula()
Range("A:A").Value = Range("A:A").Value
End Sub


Vaya con Dios,
Chuck, CABGx3
 
D

deidre

You'll have to forgive me... I'm a 'relative' novice... certainly have no
programming background. I don't understand your response.
I'd like all the comments (from cell C70) from 141 separate worksheets (all
with different names, not sequencially derived names... "S.A.12":"MC.V.11")
in one place.
I have a worksheet called "Summary", I wanted each response compiled in a
list starting in cell A80 (down to A??... there are are many worksheets
without any comments but there may be ~40 with comments).
Your help is appreciated.
deidre
 
D

Don Guillett

Forgive me.

That is exactly what I sent. Here is a mod that will also
give you each sheet name and start at row 80

Sub copyonecelltosummary()
counter = 80
For Each ws In Worksheets
With Sheets("summary")
.Cells(counter, "a") = ws.Name
.Cells(counter, "b").Value = ws.Range("c7")
End With
counter = counter + 1
Next ws
End Sub
 
Top