Vba code, copy without macro or formula

[hibernian3]

Hi there,

New to VBA, I have a basic VBA code to create a new workbook without Macros or formulas. There are 2 additional I would like to add but I am struggling with.

1 - Same execution, but Cells F47,H47 and J47 to keep active formulas (these are SUM formulas id like to keep)

2 - Can the new the new sheet which pop ups be named after cell G2 on sheet1?

Thanks, any help is appreciated, code below:


Sub Test()
Dim ws As Worksheet
Sheets(Array("Cable Tracker", "Jointing Tracker")).Copy
For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
End Sub