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" <coreymcconnell@splicetech.com.au> wrote in message
news:eLcqxmjpGHA.4424@TK2MSFTNGP05.phx.gbl...
> 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]

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

"Corey" <coreymcconnell@splicetech.com.au> wrote in message
news:e7ZMHQkpGHA.3324@TK2MSFTNGP05.phx.gbl...
> Thanks Nick.
> Perfect....
>
> CTM
>
>