How could I copy a value that is created after running some code in to
a certain cell?

My details are I run some existing code, this produces a new sheet and
a value appears in this new sheet in cell A2, this value I wish to
copy-paste special-values to Sheet1 B4

My new sheet is renamed to spmething unique each time but it will
always be created just to the right of Sheet1

Thanks