newRng.Cells.Replace What:="=SUM(#REF!)",
Replacement:="='" & _
Sh.Name & "'!G" & ActiveCell.Row, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False


becomes
Dim newRng1 as Range

set newRng1 = newRng.EntireColumn.SpecialCells(xlFormulas)
newRng1.Formula = "='" & shName & "'!" & newRng1(1).Address(0,0)

--
Regards,
Tom Ogilvy

" wrote:

Here's the example:

On the new worksheet where column G is being copied, the row number is
listed on the left, I want it to display like this:

1 Wkly
2 Total
3
4 ='wsName'!G4
5 ='wsName'!G5
6 ='wsName'!G6
7 ='wsName'!G7

but with the code above, i get this:
1 Wkly
2 Total
3
4 ='wsName'!G1
5 ='wsName'!G1
6 ='wsName'!G1
7 ='wsName'!G1

wsName is the worksheet's name where column G is being copied to new
new worksheet above.

I will continue to search but if anyone knows how to solve this
problem, I greatly appreciate the help. Thanks, Sharon