Transferring data from the same cell in many worksheets to a list in a new sheet.
Q: Is there
an easy way to copy the cell contents of cell L6 in 75 different sheets and
paste (transfer) the contents either in a column or row of a blank Excel
sheet?
A: Yes, there
is. If your sheets are numbered, there's even a solution not needing VBA.
I'll give you that one first, which assumes the names are Sheet1, Sheet2,
etc.
Make a new sheet and enter in A1 the formula:
="Sheet"&ROW(A1)
Drag this down column A to give you a column of sheet names. If you
want column heads in, then put the formula in A2, but keep the
reference in the formula to A1.
Then in B1, enter:
=INDIRECT(A1&"!L6")
If you have a header row, put this in B2 and change the A1 reference to A2.
Drag down the length of column B.
This will work pretty well with numbered sheets. Otherwise it gets trickier.
You'd need VBA to cycle through the Sheets collection and write the values
into the new list individually, like this:
Sub MakeListInNewSheet()
Worksheets.Add
ActiveSheet.Name = "Summary"
Range("a1").Select
For Each sh In Worksheets
If Not sh.Name = "Summary" Then
ActiveCell.Value = sh.Name
ActiveCell.Offset(0, 1).Formula = "='" & sh.Name & "'!L6"
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Next
End Sub
That line with all the quotes in contains some double and some single - it's
best to cut and paste this macro straight into your module to get it right.