Vertical list to table with hyperlink extraction

I have a list imported from web page (Sheet 2 in the file). I need to parse the data into a table in Sheet 3. Col. A = A7, A15, A23... in Sheet 2; Col. B = hyperlink of A7, A15, A23... in Sheet 2; Col. C= the number in A4, A12, A20... in Sheet 2; Col. D= A5, A13, A21... in Sheet 2; Col. E= the number of A8, A16, A24... in Sheet 2; Col. F= hyperlink of A8, A16, A24... in Sheet 2
As you can see everything is 8 incremental. I have the first two rows in Sheet 3 manually copied.
Can someone help me to build a VBA? Thanks
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

SiddharthRoutConnect With a Mentor Commented:

Are you sure that the data is arranged properly in sheet 2?

Click the button on Sheet 2 for the results.


Code Used

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long
    Set ws1 = Sheets("Sheet2")
    Set ws2 = Sheets("Sheet3")
    ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
    For i = 1 To ws1LastRow Step 8
        On Error Resume Next
        ws2.Range("A" & ws2LastRow).Value = ws1.Range("A" & i).Offset(6).Value
        ws2.Range("B" & ws2LastRow).Value = ws1.Range("A" & i).Offset(6).Hyperlinks(1).Address
        ws2.Range("C" & ws2LastRow).Value = OnlyNumbers(ws1.Range("A" & i).Offset(3).Value)
        ws2.Range("D" & ws2LastRow).Value = ws1.Range("A" & i).Offset(4).Value
        ws2.Range("E" & ws2LastRow).Value = ws1.Range("A" & i).Offset(7).Value
        ws2.Range("F" & ws2LastRow).Value = ws1.Range("A" & i).Offset(7).Hyperlinks(1).Address
        ws2LastRow = ws2LastRow + 1
        On Error GoTo 0
End Sub

Function OnlyNumbers(strInput As String) As String
    Dim strChar As String, strOutput As String
    strOutput = ""

    For i = 1 To Len(strInput)
        strChar = Mid(strInput, i, 1)
        If (IsNumeric(strChar)) Then
            strOutput = strOutput & strChar
        End If
    Next i
    OnlyNumbers = strOutput
End Function

Open in new window

I think I almost understand the problem - One question -

What do you mean by 'hyperlink of...' do you mean hyperlink TO?
mmcompactAuthor Commented:
yes, so eg. A2 is "sparkzck" from A7 in Sheet2, B2 is "", which is the address you will get when you click on A7 in Sheet2.
And also, when I say "the number of", it means I want to extract the numeric number from the string of whichever cell it belongs
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

mmcompactAuthor Commented:

Here's the page I want to populate the table from. Is there a way to solve this problem?
mmcompact: I have no issues in sharing points if someone has helped but I couldn't understand your choice for the "Assisted Solution"? How does that assist you in your query?

mmcompactAuthor Commented:
When I accepted your solution, the site didn't let me assign all the 500 points to you, I must pick one more. So I just put your 20 points to the other guy. I don't know another way to go around it. Kinda new here, sorry
All Courses

From novice to tech pro — start learning today.