Is there a way to auto-populate information from newly added worksheets to the master worksheet

[Malrose]

Hello,

This question may of been asked many times before and I have tried to look for resolutions, but non of the formulaes seem to work.

I'm using Office 365 on a Samsung tablet.

I'm trying to create a file where when I carry out a new project I will duplicate a worksheet, amend the figures, but I want the total figure to show on my summary page.

So for example:
I have a master sheet named 'Summary' and then each worksheet will be named P1, P2, etc and when I add a work sheet I will go up to the next number.

On the summary page I will have P1, P2, P3 listed etc. For the purpose of P1, I want the figure from cell F2 to show on the summary page C4, and then for P3 I want F2 to show on cell C5.

Is there a way to do this, because I happened to come across a post which said 365 has stopped the indirect function for this.

I would like this to auto-populate for new entries of worksheets I add, so eventually P100 will automatically add to the summary page once the worksheet has been created.

Any help will be high appreciated.

Thank you.

 

[JLGWhiz]

In which column of the Summary sheet will you have the P1, P2, P3...Pn sheets listed? Is the objective to put the F2 value of each P# sheet into column C of the summary sheet on the corresponding row for each P# sheet?
This assumes column A as the column for the P# sheets. it would post the value to the summary sheet each time the user exits a P# Sheet. Goes into the ThisWorkbook code module.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sheets("Summary").Range("A:A").Find(Sh.Name, , xlValues, xlWhole).Offset(, 2) = Sh.Range("F2").Value
End Sub

 

[JLGWhiz]

This would work better.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
On Error Resume Next
Sheets("Summary").Range("A:A").Find(Sh.Name, , xlValues, xlWhole).Offset(, 2) = Sh.Range("F2").Value
On Error GoTo 0
Ert.Clear
End Sub

 

[Malrose]

Thank you for responding so quickly. P1 would be in column B4.

 

[JLGWhiz]

The code below is Workbook event code which should be copied to the ThisWorkbook code module. The code is activated when a worksheet is deactivate by switching to a different sheet or closing the file. When the sheet is deactivated, the procedure will look in the Summary sheet, column B for the sheet name (P#) and if found will then post the value of cell F2 of the P# sheet to the corresponding cell in column C of the Summary sheet.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
On Error Resume Next
Sheets("Summary").Range("B:B").Find(Sh.Name, , xlValues, xlWhole).Offset(, 1) = Sh.Range("F2").Value
On Error GoTo 0
Ert.Clear
End Sub

 

[Malrose]

Thank you.

Sorry for the delay in getting back to you.

I have just managed to get onto a computer to try this out, unfortunately I have an error of 424 runtime.