Web Scraping With Loops

[qcan]

Hi,

I need some help & guidance please.

Although I can record a macro and make minor modifications with the
code, I know next to nothing about VBA programming. I am looking for a
simple macro (I hope) that would look into a specific sheet (called
main) that would contain a unique URL on each line that I would like
to inport into another spreadheet (called MAIN2). Each time it does
this, I would like it to add to column "Z" the URL that it pertains
to. Once done, it would once again go to main and look for the next
line that conatins yet another unique URL and then would return the
data back to the next available row etc etc.

Thanks.

 

[JLGWhiz]

Are all of the unique URLs in a single column in Sheets("Main")?

If so, assume they are in column A. If they are in another column you
will neet to modify the code below:

Sub URLMain2()
Dim c As Range, lr As Long
lr = Sheets("Main").Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Sheets("Main").Range("A2:A" & lr)
If Not c Is Nothing Then
c.Copy Sheets("Main2").Range("Z" & Cells(Rows.Count, 26) _
.End(xlUp).Row + 1)
End If
Next
End Sub

 

[qcan]

Are all of the unique URLs in a single column in Sheets("Main")?

If so, assume they are in column A.  If they are in another column you
will neet to modify the code below:

Sub URLMain2()
Dim c As Range, lr As Long
      lr = Sheets("Main").Cells(Rows.Count, 1).End(xlUp).Row
      For Each c In Sheets("Main").Range("A2:A" & lr)
         If Not c Is Nothing Then
            c.Copy Sheets("Main2").Range("Z" & Cells(Rows.Count, 26) _
              .End(xlUp).Row + 1)
         End If
      Next
End Sub








- Show quoted text -

Yes, your assumptions are correct. Unfortunatly, I got a runtime error
"9" when executing it (subscript out of range). The erro appear to be
on this line....

lr = Sheets("Main").Cells(Rows.Count, 1).End(xlUp).Row

Sorry... I am not sure how to fix this. Can you help ?

Thanks.