Furthermore, I would like to delete any sheets, which name is not included
within the lists under column B of sheet "Date" and the Date sheet cannot be
deleted too. Do you have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

"Jacob Skaria" wrote:

Hi Eric

Try the below

Sub MyMacro()
Dim lngRow As Long, ws As Worksheet, wsDate As Worksheet

Set wsDate = Sheets("Date")
For lngRow = 2 To wsDate.Cells(Rows.Count, "B").End(xlUp).Row

If Not SheetExists(wsDate.Range("b" & lngRow)) Then
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
ws.Name = wsDate.Range("b" & lngRow)
Else
Set ws = Sheets(wsDate.Range("b" & lngRow).Text)
End If

ws.Hyperlinks.Add ws.Range("A1"), wsDate.Range("c" & lngRow), _
TextToDisplay:=wsDate.Range("c" & lngRow).Text

Next
End Sub

Function SheetExists(strSheet As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets(strSheet)
If Not ws Is Nothing Then SheetExists = True
End Function

--
Jacob (MVP - Excel)


"Eric" wrote:

Do you have any suggestions on how to create a loop to retrieve the link and
insert into specific sheet one at a time until the end of the list?
Thank everyone very much for any suggestions
Eric

"ozgrid.com" wrote:

Sub AddSheet100()
Dim ws As Worksheet

On Error Resume Next
Set ws = Sheets("100")
On Error GoTo 0

If Not ws Is Nothing Then
MsgBox "Sheet called '100' already exists"
Else
Sheets.Add().Name = "100"
End If

End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"Eric" wrote in message
...
Do you have any suggestions on how to code macro to do that?
My list contains 100 links, and furthermore, I would like to check if
sheet
name (100) is not available within this workbook, then insert a new sheet
and
name (100).
Do you have any suggestions on how to code it in macro?
Thanks in advance for any suggestions
Eric

"Jacob Skaria" wrote:

Hi Eric

If you have numerics in ColB of Sheet 'Date' try the below. Please make
sure
you try the below in a saved workbook. For the sheet named "1" the
formula
will return the corresponding link as a hyperlink...

=HYPERLINK("http://" &
VLOOKUP(INT(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"")),Date!B:C,2,0))

or else try
=HYPERLINK("http://" &
VLOOKUP(REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),""),Date!B:C,2,0))

--
Jacob (MVP - Excel)


"Eric" wrote:

Does anyone have any suggestions on how to import a list of links in to
Excel?
Under the sheet "Date", there is a list of http links under column C,
and a
list of name under column B, I would like to import each link into
specific
sheet at cell A1. For example, in sheet "Date", there is a link
www.cnn.com
in cell C2, and 1 in cell B2, so this link is imported into sheet "1",
and
keep running the rest of links.
Does anyone have any suggestions on how to do it in Excel macro?
Thanks in advance for any suggestions
Eric