New Worksheet Values Auto Displayed on Sheet 1

[Corey]

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

 

[Corey]

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....

 

[NickHK]

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]

Thanks Nick.
Perfect....

CTM

 

[NickHK]

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