When creating a new excel sheet, how can some specific cells appear in another sheet?

Question

Good day all,

I need your help for a project please.

Pls find the link if this can help: https://1drv.ms/x/s!AtYjpXHdhdJHmF9DcIIsgyPob3bk?e=PZvUW3

When creating sheet P.O No.1, we want to have the below cells to automatically appear in sheet P.O Statement

P.O No.1 to P.O Statement

J3 to B21

J5 to C21

D10 to D21

L25 to H21

M40 to I21

If possible, same to apply automatically to sheet P.O Statement, each time we create a new P.O No.. sheet.

P.O No.2 to P.O Statement

J3 to B22

J5 to C22

D10 to D22

L25 to H22

M40 to I22

And it goes on for each additional sheet P.O No....

Thank you for your assistance.

Rgds

Patrick.

Answer 1

One easy way to do this is with a macro. Once the new worksheet is created and active, invoke the macro via shortcut key or Alt-F8. In the macro:

• set a worksheet object (named target) to the P.O Statement worksheet.
• set a string variable (named source) to the name of the new worksheet
• set an integer variable (named count) to number of the new worksheet that can be extracted from the end of string source Then, for source cells whose value should copied to target, code macro statements of the form

target.range("B21").offset(0,count-1).formula = "=" & source & "!J3"
target.range("C21").offset(0,count-1),formula = "=" & source & "!J5"

Answer 2

Dear Barry,

Thanks for helping. Got an error message, highlighting the 2nd line below in yellow.

Not too sure where is the error.

Private Sub CommandButton1_Click()

Recap.Range("B21").Offset(0, Count - 1).Formula = "=" & P.O & "!J3"

Recap.Range("C21").Offset(0, Count - 1).Formula = "=" & P.O & "!J5"

End Sub

Can this link help to check pls?

https://1drv.ms/x/s!AtYjpXHdhdJHmGgWegFPjfkJRDDH?e=na48kG

Thank you.

Patrick