Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
lee lee is offline
external usenet poster
 
Posts: 184
Default Copying Cells From Multiple Worksheets to Create Summary Sheet

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,
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 427
Default Copying Cells From Multiple Worksheets to Create Summary Sheet

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summary of multiple spreadsheets: Peter Excel Worksheet Functions 2 January 4th 06 06:09 AM
Multiple Workbook Data Capture Summary Sheet lgmack Excel Discussion (Misc queries) 1 October 6th 05 05:11 PM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
How do I display data from multiple worksheets in a summary sheet. stardust Excel Discussion (Misc queries) 1 March 18th 05 01:26 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM


All times are GMT +1. The time now is 05:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"