Copying Cells From Multiple Worksheets to Create Summary Sheet

[Guest]

I have multiple worksheets that have an identical form that different people
fill out. I would like to create a summary sheet that pulls from the same
cells in each of the different worksheets. Is there to create a column on
the worksheet that increments by a worksheet as it goes down. For example
lets say I want to pull cell A20 from each worksheet i would want Sheet1 A20,
Sheet2 A20, etc going down the column. I am doing this manually which is
very tedious.

Thanks for any help,

 

[Dave O]

Hi, Lee-
I'm not aware of any way to do this without VBA code- so I wrote the
following code to do that task. Copy and paste this into your file as a
macro. This will create a formula that references cell A20 from each
tab in the workbook. You should try this out on a backed up copy of
your data to avoid catastrophic data loss.

Sub Three_D_Tab_Formula()
Dim SheetName As Worksheet

For Each SheetName In Sheets
ActiveCell.Formula = "='" & SheetName.Name & "'!a20"
ActiveCell.Offset(1, 0).Select
Next SheetName

End Sub