moving columns to a new sheet

[craigmacca]

i have the workbook attached,

in sheet1 i have a list of hyperlinks in column A, and Names in column
B

what i want to do is in sheet 2

column A - shows the Column A from sheet 1 without the hyperlink

Coulmn B - Shows the Column A Actual hyperlink url

Coulmn C - Shows Column B from sheet 1

i know i need a macro to do this but not sure how to do this, i have
attached a small example of what i need

many thanks

Craig


+-------------------------------------------------------------------+
|Filename: test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5013 |
+-------------------------------------------------------------------+

 

[jetted]

Hi

You may want to try this

Sub copy_sheet1_sheet2()
Sheets("Sheet1").Select
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To rowcount
Sheets("Sheet1").Select
Range("a" & i).Select
val_a = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
val_c = ActiveCell.Value
ActiveCell.Offset(0, -1).Select
val_b = ActiveCell.Hyperlinks(1).Address
Sheets("Sheet2").Select
Range("A" & i).Select
ActiveCell.Value = val_a
Range("b" & i).Select
ActiveCell = val_b
Range("c" & i).Select
ActiveCell = val_c
Next
Call make_hyperlink
End Sub
Sub make_hyperlink()
Sheets("Sheet2").Select
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To rowcount
Range("b" & i).Select
adr = ActiveCell.Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=adr, _
TextToDisplay:=adr
Next
End Su