Multiple choice guessing
Knowledge measurement audit
Item bank services
Excel and Office RAQ
Free Excel Add-in
Free PowerPoint Add-in
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
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,
Make a new sheet and enter in A1 the formula:
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:
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:
ActiveSheet.Name = "Summary"
For Each sh In Worksheets
If Not sh.Name = "Summary" Then
ActiveCell.Value = sh.Name
ActiveCell.Offset(0, 1).Formula = "='" & sh.Name & "'!L6"
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.
other RAQ topics