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

"(E-Mail Removed)" 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
>
> "(E-Mail Removed)" 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