Marco to copy and paste column as well as edit link
 

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

Marco to copy and paste column as well as edit link
 

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

"Harn88" wrote:

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

Marco to copy and paste column as well as edit link
 

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

"Harn88" wrote:

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