Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Worksheet Values Auto Displayed on Sheet 1
I have a WorkBook that has a macro that copies worksheet2, and adds it to
the workbook as worksheet3. Want i want to do is have on WorkSheet1 display cells B3 & Z48 values of EACH worksheet in the workbook. Like: Tab Name Name Location Sheet1 Fred Bedrock Sheet2 Jack BeanStalk Sheet3 Adam Eden I tried this with simple formula, but IF the worksheet DID NOT exist yet, i get an error, because it cannot find the sheet. Any idea's how i can display these values from each sheet and each new sheet on Sheet1? Regards Corey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Worksheet Values Auto Displayed on Sheet 1
Sorry forgot to add, i will have up to 49 sheets in the book before i start
a new workbook. So values from sheets 2-49 need to be displayed on sheet1. Corey.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Worksheet Values Auto Displayed on Sheet 1
Corey,
Something like: Private Sub Workbook_NewSheet(ByVal Sh As Object) Dim i As Long For i = 2 To Worksheets.Count With Worksheets(1).Range("A1") .Offset(i, 0).Value = Worksheets(i).Name .Offset(i, 1).Value = Worksheets(i).Range("B3").Value .Offset(i, 2).Value = Worksheets(i).Range("Z48").Value End With Next End Sub NickHK "Corey" wrote in message ... I have a WorkBook that has a macro that copies worksheet2, and adds it to the workbook as worksheet3. Want i want to do is have on WorkSheet1 display cells B3 & Z48 values of EACH worksheet in the workbook. Like: Tab Name Name Location Sheet1 Fred Bedrock Sheet2 Jack BeanStalk Sheet3 Adam Eden I tried this with simple formula, but IF the worksheet DID NOT exist yet, i get an error, because it cannot find the sheet. Any idea's how i can display these values from each sheet and each new sheet on Sheet1? Regards Corey |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Worksheet Values Auto Displayed on Sheet 1
Thanks Nick.
Perfect.... CTM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Worksheet Values Auto Displayed on Sheet 1
Corey,
This code assumes that the first worksheet [Worksheets(1)], does not contain data. Also that when you add/copy worksheets you specify the location so they do not appear as Worksheets(1). NickHK "Corey" wrote in message ... Thanks Nick. Perfect.... CTM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up in another sheet for values to return in 1st worksheet | Excel Discussion (Misc queries) | |||
Zero(0) values box checked-but not displayed as a 1st digit ? | Excel Worksheet Functions | |||
Comparing displayed values | Excel Discussion (Misc queries) | |||
displayed cell values | Excel Worksheet Functions | |||
Beginner - Excel - Auto look up values from worksheet 3 from User | Excel Programming |