I have the following code:
------------
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name < newWs.Name And Sh.Name < aWs.Name And Sh.Name
< bWs.Name Then

Set rng = Nothing
On Error Resume Next
Set rng = Sh.Range("lblWTotal")
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Sheet " & Sh.Name & " does not contain
lblWTotal"
Else
Sh.Range("lblWTotal").Value = Sh.Name
End If


Sh.Range("G:G").Copy
newRng.PasteSpecial


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


Sh.Range("lblWTotal").Value = "Wkly"

Set newRng = newRng.Offset(0, 1)


End If


Next
------
Basically it copies the column G from every worksheets to another
worksheet. On the new worksheet I want to replace the cells value to
the actual worksheet/cell number

How do i add the actual row number to ='wsName'!G

Using ActiveCell.Row adds 1 to all the cells. I know of Row() but I
dont know how to use Row() in VB.

Thank you,

Sharon

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

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

this assumed that newRng was is column G. If it isn't, then here is a
modification


Dim newRng1 as Range
Dim r as Range
set newRng1 = newRng.EntireColumn.SpecialCells(xlFormulas)
set r = newRng1.parent.Cells(newRng1(1).row,"G")
newRng1.Formula = "='" & shName & "'!" & r.Address(0,0)

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

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

Thank you so much Tom!!! You're a life saver. Now, I just need to
understand what each line means/do but Im sure online help will be
able to help me.

Thanks again.
Sharon

This one works.

Dim newRng1 as Range
Dim r as Range
set newRng1 = newRng.EntireColumn.SpecialCells(xlFormulas)
set r = newRng1.parent.Cells(newRng1(1).row,"G")
newRng1.Formula = "='" & shName & "'!" & r.Address(0,0)

--
Regards,
Tom Ogilvy