how to use reference cell in VB?

[azu_daioh]

I have this code that works great on one workbook but I have to modify
it for another workbook.

Here's the code I have/want to modify:
----------------
Dim newRng As Range
Dim newWs As Worksheet
Dim rng As Range

Set newWs = Worksheets("GTOTAL")
Set aWs = Worksheets(1)
Set bWs = Worksheets(2)
Set newRng = newWs.Range("C:C")

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 xlPasteValues
newRng.PasteSpecial xlPasteFormats

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


Set newRng = newRng.Offset(0, 1)
End If
Next
-------------

Now instead of pasting the value and formats, I would like it to paste
the a reference of the cell it copied.

For example if the first SH is 205
GTOTAL!C4 = 205!G4.
GTOTAL!C5 = 205!G5

then on next SH = 206
GTOTAL!D4 = 206!G4
GTOTAL!D5 = 206!G5

and so on

Is this possible? I tried searching and using the codes I find here
and could not make it work. Please help. Thank you.

 

[NOPIK]

GTOTAL!C4 = 205!G4.
GTOTAL!C5 = 205!G5

then on next SH = 206
GTOTAL!D4 = 206!G4
GTOTAL!D5 = 206!G5

and so on

Is this possible? I tried searching and using the codes I find here
and could not make it work. Please help. Thank you.

I use
Cells.Replace What:="OtherSheet!", Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

after .PasteSpecial