Thread: hyperlinks

Hey

Ive placed it it the code

Option Explicit
Sub Port()
   Dim sht1 As Worksheet
   Dim qryTbl As QueryTable
   Dim XRow As Variant
   Dim Nrow As String
   Set sht1 = ThisWorkbook.Worksheets("Here")

    For Each XRow In Range("B2", Cells(Rows.Count, 2).End(xlUp))' it replaces this bit
    Nrow = XRow.Address
   Set qryTbl = sht1.QueryTables.Add(Connection:="URL;" & XRow, _
   Destination:=sht1.Range("A1"))

   With qryTbl
       .BackgroundQuery = True
       .WebSelectionType = xlSpecifiedTables
       .WebTables = "1,3"
       .WebFormatting = xlWebFormattingAll
       .Refresh BackgroundQuery:=False
       .SaveData = True
   End With
  
   With sht1
      .Range("C1").Copy Destination:=Sheets("links").Range(Nrow).Offset(0, 1)
   .Range("B7").Copy Destination:=Sheets("links").Range(Nrow).Offset(0, 2)
   End With
    Sheets("Here").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Sheets("links").Select
Next XRow
End Sub

HI,
this makro is very time consuming. does anyone know how to speed up this process? maybe to use just one worksheet, instead of two would help a bit...
maybe someone has any better ideas. I appreciate any advise or update in the code.
THANKS

sub snb()
  sq=application.transpose(columns(2).specialcells(2))
  With ThisWorkbook.sheets("Here")
    For j=2 to ubound(sq)   
      If .querytables.count=0 then 
        With .QueryTables.Add("URL;" & sq(1),.[A1])
          .WebSelectionType = xlSpecifiedTables
          .WebTables = "1,3"
          .WebFormatting = xlWebFormattingAll
          .Refresh False
          .SaveData = True
        End With
      else
        With .querytables(1)
          .connectionstring=sq(j)
          .refresh False
        End with
      End if
      Do
        Doevents
      Loop until .[C1]<>""

      Sheets("links").Range(j).Offset(0, 1)=.[C1]
      Sheets("links").Range(j).Offset(0, 2)=.[B7]
      .[C1].clearcontents
    End With
  Next
End Sub

No objection to your analysing the code and fixing this error.

Move the "Next" above the "end with"

I was just saying that those two lines were in the wrong order. I make no comment about the functionality of the code itself.