Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate cell values with sheet names
This is a simple example. Customize to suit. This example uses the
Workbook's NewSheet event to update the summary sheet when a new sheet is added to the workbook. You will need to "refresh" the summary sheet, by calling Summarize separately to update data. You may want to manually refresh the summary sheet instead of having the NewSheet event trigger an update, or you may want to add refresh triggers on other workbook or worksheet events. Up to you. Summarize rewrites all the data. You may want to use Summarize to refresh the entire sheet, but supply other procedures to merely add new sheet names to the list when sheets are added or to add or update data when it changes. There are many possibilities. Look at Worksheet and Workbook events for possible update triggers. 'In a standard module Sub Summarize() Dim rowIndex As Long rowIndex = 0 Dim wks As Worksheet Dim wksSummary As Worksheet Set wksSummary = ThisWorkbook.Worksheets("Summary") 'assumes a worksheet named "Summary" For Each wks In ThisWorkbook.Worksheets If Not wks Is wksSummary Then rowIndex = rowIndex + 1 'copy over some data 'could also insert formulas into summary sheet referencing other worksheet cells wksSummary.Columns(1).Rows(rowIndex).Value = wks.Name wksSummary.Columns(2).Rows(rowIndex).Value = wks.Range("A1").Value wksSummary.Columns(2).Rows(rowIndex).Resize(1, 2).Value = wks.Range("B1:C1").Value End If Next wks End Sub 'In ThisWorkbook module Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.ScreenUpdating = False Sh.Move After:=Sheets(Sheets.Count) Call Summarize ThisWorkbook.Worksheets("Summary").Activate Application.ScreenUpdating = True End Sub "Tracy W." <Tracy wrote in message ... I am trying to build a worksheet that will take the names of the subsequent worksheets in the file, and populate new rows for each of the names and possibly pull data from certain cells on those sheets to form a "summary" page if you will. Problem is, I have no idea how to pull the names of the worksheets, and automatically create new rows on this summary page. Obviously, this can be done by linking the cells, but this is a manual process, and can be tedious. I'd like to have it where if I add or delete a worksheet, the summary page is automatically updated, and I don't have to keep making changes. Any ideas are greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate excel sheet based on values in a separate sheet | Excel Discussion (Misc queries) | |||
Can Excel calculate populate table using row/column values & calc's on other sheet? | Excel Discussion (Misc queries) | |||
Concatenating cell values to create sheet names | Excel Discussion (Misc queries) | |||
Populate cell values with sheet names | Excel Programming | |||
Want to be able to click on a cell and have a list box appear to give values to populate a cell | Excel Programming |