Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to cycle through worksheets
I want to create a button activated macro to pull the worksheet name from all
the worksheets in a workbook and summarize them into the final worksheet with associated functions to perform on the worksheet data. I want the macro to cycle through any worksheets the workbook may contain and on the summary sheet caculate data from the worksheet. Caveat - I won't know the names of most of the worksheets before creating the macro. For example: The workbook contains Sheet1, Sheet2, and Sheet3. On sheet 3, I want to place summary data for Sheet1 and Sheet2 as individual rows. In Sheet3!A2 - I want to place the name of one of the worksheets (e.g., Sheet1), in cell Sheet3!B2 - calculate the sum of a cell range from the same worksheet (Sheet1!L2:L17), followed by another function. Then cycle to the next worksheet (e.g., Sheet2) adding the same information beginning in Sheet3!A3. Thanks for any help you can provide. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to cycle through worksheets
Untested:
Sub Summarize() Dim ws As Worksheet Worksheets.Add , Worksheets(Worksheets.Count) For Each ws In Worksheets If ws.Index < Worksheets.Count Then With Worksheets(Worksheets.Count) .Cells(ws.Index + 1, 2) = ws.Name .Cells(ws.Index + 1, 3) = WorksheetFunction.Sum(ws.Range("L2:L17")) End With End If Next End Sub __________________________________________________ ________________________ "tpmax" wrote in message ... I want to create a button activated macro to pull the worksheet name from all the worksheets in a workbook and summarize them into the final worksheet with associated functions to perform on the worksheet data. I want the macro to cycle through any worksheets the workbook may contain and on the summary sheet caculate data from the worksheet. Caveat - I won't know the names of most of the worksheets before creating the macro. For example: The workbook contains Sheet1, Sheet2, and Sheet3. On sheet 3, I want to place summary data for Sheet1 and Sheet2 as individual rows. In Sheet3!A2 - I want to place the name of one of the worksheets (e.g., Sheet1), in cell Sheet3!B2 - calculate the sum of a cell range from the same worksheet (Sheet1!L2:L17), followed by another function. Then cycle to the next worksheet (e.g., Sheet2) adding the same information beginning in Sheet3!A3. Thanks for any help you can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cycle through worksheets and sum | Excel Programming | |||
How do I cycle through all of the worksheets | Excel Discussion (Misc queries) | |||
How do I cycle through all of the open worksheets? | Excel Discussion (Misc queries) | |||
Cycle through all worksheets | Excel Programming | |||
Cycle thru worksheets in a workbook | Excel Programming |