Coding 2 loops in VBA

[DMaynard]

Hi,

I don't have a lot of experience with macros but I managed to build the start of something that works using the macro recorder (See below). The issue that I'm running into now is that I want to code a loop into the macro. I have a list of websites in column G and I want to open them in order (i.e. I'm selecting G2 in the first step here, but after it runs once I want it to loop back and do G3, and so on). Similarly I paste my values into sheet 2 cell B2, and on the next loop I'd like it to end up in B3, B4, etc.. Ideally I'd like it to loop 1000 times.

Thanks in advance for any help you can offer!

Sub Macro10()
'
' Macro10 Macro
'


'
    Range("G2").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Range("P3").Select
    Range("A2:F2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B3").Select
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub

 

[Osvaldo Palmeiro]

I'm afraid I did not quite understand about the loop that you want to replicate data from "A2:F2" to "Sheet2", anyway here goes a code as a starting point.

Sub Macro10V2()
 Dim Hyperweb As Range

' Macro10V2 Macro
'
  For Each Hyperweb In Range("G2:G" & Cells(Rows.Count, 7).End(3).Row)
    Hyperweb.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    
    Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2).Resize(, 6).Value = Range("A2:F2").Value
    
  Next Hyperweb
End Sub

 

[DMaynard]

I'm afraid I did not quite understand about the loop that you want to replicate data from "A2:F2" to "Sheet2"

Thanks, basically I have a long list of webpages in column G. I want to access the first webpage and paste the contents of the page into column P. I then have formulas in A2:F2 that extract data from the webpage text. I want to copy the data that I extract with those formulas from A2:F2 on Sheet 1 to B2:G2 on Sheet 2.

I tried running the code you sent and the looping part worked great. But I ran into 2 problems. (1) The code opens up 1000's of web pages-- Ideally I'd like to close the page after copying the content of the page, and (2) the code seems to only copy the information from the second webpage on the list to the table on sheet 2 (not sure why this is happening, I ran into the same issue when I tried to use the macro recorder myself).

I'd really appreciate any advice you can give. Thanks so much for your help!

 

[DMaynard]

Thanks, basically I have a long list of webpages in column G. I want to access the first webpage and paste the contents of the page into column P. I then have formulas in A2:F2 that extract data from the webpage text. I want to copy the data that I extract with those formulas from A2:F2 on Sheet 1 to B2:G2 on Sheet 2.

Sorry, should add that after it does one iteration of that I'd like it to loop back and do the next webpage and copy the info into B3:G3 on sheet 2, etc...