I have a nice excel macro below that passes cell values on Input sheet (Ins)
to Output sheet (OS).
The macro examines Output sheet Column A for rows having some ..... and then
writes the appropriate InS row values to the ..... row. When 1 InS row is
written the macro increments to the next correct row pairs.
It works nicely with 20,000 row input sheets.

I want to alter the macro below so that an address is passed rather that a
value and leaves the InS and OS permanently linked. Verbally put:

InS.Range(InS.Cells(datarow, "A"), InS.Cells(datarow, "E")).Copy
Destination:=OS.Cells(i, "C")

needs to be altered to permanently write the correct cell addresses rather
than cell values.

Thanks ahead of time,

John

Macro

Sub In2Out()
'
' In2Out Macro
' Macro recorded 5/42006 by John Birken
Dim OS, InS As Worksheet

Set OS = Sheets("sheet3")
Set InS = Sheets("sheet2")

lastrow = OS.Cells(Rows.Count, "A").End(xlUp).Row
datarow = 1
For i = 10 To lastrow
If Left(OS.Cells(i, "A"), 4) = "...." Then
InS.Range(InS.Cells(datarow, "A"), InS.Cells(datarow, "E")).Copy
Destination:=OS.Cells(i, "C")
datarow = datarow + 1
End If
Next i

End Sub

Nel post
*John* ha scritto:

I have a nice excel macro below that passes cell values on Input
sheet (Ins) to Output sheet (OS).
The macro examines Output sheet Column A for rows having some .....
and then writes the appropriate InS row values to the ..... row. When
1 InS row is written the macro increments to the next correct row
pairs.
It works nicely with 20,000 row input sheets.

I want to alter the macro below so that an address is passed rather
that a value and leaves the InS and OS permanently linked. Verbally
put:

InS.Range(InS.Cells(datarow, "A"), InS.Cells(datarow, "E")).Copy
Destination:=OS.Cells(i, "C")

needs to be altered to permanently write the correct cell addresses
rather than cell values.

Thanks ahead of time,

John


Hi John,

I'm not sure, but I think you could use this line of code to link the two
cells:

OS.Cells(i, "C").Formula = "=" & InS.Range(InS.Cells(datarow, "A"),
InS.Cells(datarow, "E")).Address



--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy