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

Malrose

New Member
Joined
Feb 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Mobile
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This would work better.

VBA Code:
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

New Member
Joined
Feb 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Mobile
Thank you for responding so quickly. P1 would be in column B4.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Feb 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top