Marco to copy and paste column as well as edit link

[Harn88]

A macro to copy and paste column as well as replace the formula/link in those
cells

For example:
I want to copy column A to column B with all the same format however with
different link in the cells.

Column A, all cells is link to Q:\ PPE20081202, so when I hit the Marco it
will copy the entire format to column B but the link will change from Q:\
PPE20081202 to Q:\ PPE20081216

P.S
20081216 is 2008-12-16 (Date)

Thank you very much

Best regards,

Harn

 

[Mike]

try this
Sub test()
Dim rng As Range
Dim Cell As Range
Dim strNewVal, strDate As String

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
strDate = Format(Date, "yyyymmdd")
For Each Cell In rng
strNewVal = Replace(Cell.Value, Right(Cell.Value, 8), strDate)
Cell.Offset(, 1).Value = Cell.Value
Next Cell
rng.ClearContents
End Sub

 

[Mike]

Replace this line Cell.Offset(, 1).Value = Cell.Value with the one below
Cell.Offset(, 1).Value = strNewVal