Excel and Office RAQ

Transferring data from the same cell in many worksheets to a list in a new sheet.


Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing


Knowledge measurement audit

Item bank services

Item design course








Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in


Home page

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:


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:

   Sub MakeListInNewSheet()
   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"
   ActiveCell.Offset(1, 0).Range("A1").Select
   End If

   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.

Back to other RAQ topics