Sub Step4_Copy4Path()
Dim cel As Range, ws As Worksheet
Set ws = Sheets("path")
'START of confirmation message box'
response = MsgBox("Run Macro for" & vbNewLine & "Step 1: CopyRange?", vbYesNo)
If response = vbNo Then
MsgBox ("Macro Canceled!")
Exit Sub
End If
'END of confirmation message box'
' Code 1: Copy sheet
Application.ScreenUpdating = False
With Sheets("CopyTranpose") 'Source worksheet name
Set DestSh = Sheets("path") 'Destination worksheet name
lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
.Range(.Cells(2, "F"), .Cells(lr, "F")).Copy DestSh.Cells(2, "A") 'Ir = last column of x
.Range(.Cells(2, "E"), .Cells(lr, "E")).Copy DestSh.Cells(2, "C")
.Range(.Cells(2, "D"), .Cells(lr, "D")).Copy DestSh.Cells(2, "D")
.Range(.Cells(2, "C"), .Cells(lr, "C")).Copy DestSh.Cells(2, "E")
.Range(.Cells(2, "A"), .Cells(lr, "A")).Copy DestSh.Cells(2, "I")
.Range(.Cells(2, "B"), .Cells(lr, "B")).Copy DestSh.Cells(2, "J")
End With
Application.ScreenUpdating = True
Set ws = Sheets("path")
With ws.Range("A1:E" & Range("C" & Rows.Count).End(xlUp).Row)
ws.Columns(1).Value = Evaluate(Replace(Replace(Replace("IF(ISNUMBER(SEARCH(""|""&#C&""|"",""|Poster|Index|"")),[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E]#E[/URL] ,[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A]#A[/URL] )", "#C", .Columns(3).Address), "[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E]#E[/URL] ", .Columns(5).Address), "[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A]#A[/URL] ", .Columns(1).Address))
ws.Columns(5).Value = Evaluate(Replace(Replace("IF(ISNUMBER(SEARCH(""|""&#C&""|"",""|Poster|Index|"")),"""",[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E]#E[/URL] )", "#C", .Columns(3).Address), "[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E]#E[/URL] ", .Columns(5).Address))
End With
End
End Sub