Macro to go loop through a column of URL's and paste data from each to new row

[Kc7487]

Hello Mr. Excel world!

Long time reader first time account maker/poster.

I have a workbook with a number of different Worksheets.

I have a column (Column M) on a worksheet called "AM INTL" Briefing. This column contains a hyperlink on each row (none are blank right now, but there could be blank rows in the column sometimes)

I am attempting to create a macro loop which will look at each URL, change the web query already added to the worksheet "UFDImport" to reflect that URL, refresh the web query (if that part is necessary to make the query match the information on the website), and then copy some specific cells and paste to a new row.

The cells I want to copy are NOT part of the web query cells, instead they are cells which do some filtering of the data down to the part I need. There is another Worksheet called "UFD MANIPULATION" which at the top is all just referencing the web query cells. Below the range that matches the web query though, are some formulas that filter down the data to the parts I need. Cell E20 and cell E21 on this worksheet are the cells I want to copy and paste into rows G and H on the "AM INTL Briefing" worksheet.

IE, I would like the macro to change the web query to reference the URL in cell M4 of "AM INTL Briefing", and then refresh it so that cells E20 and E21 of worksheet "UFD MANIPULATION" now contain the data from the website. Then I would like the macro to copy E20 of "UFD MANIPULATION" and paste it to cell G4 of "AM INTL Briefing" worksheet, and copy E21 of "UFD MANIPULATION" and paste it to cell H4 of "AM INTL Briefing". I would then like the macro to change the web query to reference the URL in cell M5 of "AM INTL Briefing" and refresh so that E20 and E21 on "UFD MANIPULATION" now reflect the new data from the next URL. Then I would like the macro to again copy cell E20 from "UFD MANIPULATION" and paste to cell G5 of "AM INTL Briefing" and copy cell E21 from "UFD MANIPULATION" and paste to cell H5 of "AM INTL Briefing".

I would like the macro to continue doing this until there are no more URLs in column M (may be only a few URLs, may be hundreds, with some blanks in between.)

I will paste my code below, but I fear I am missing something important, (I am still learning how to code macros). It seems too short and is erroring out with "Object doesn't support this property or method" on the line ".Connection = "URL;" & .Cells(i, 12).Value"

Thanks for any and all help. I appreciate all I have learned from reading this website thus far. I hope this is a clear enough first question, which will help me to learn more and hopefully help others in the future with this as well. Let me know if more information is needed please.

Thanks!

My current non-working code below:

Sub DynamicUFD()

Dim i As Integer

For i = 1 To 6

With Sheets("UFDImport").QueryTables(1)
    .Connection = "URL;" & .Cells(i, 12).Value
    
    .Refresh
    End With

ActiveWorkbook.RefreshAll


'After the workbook refresh, my formulas on the page called UFD Manipulation should refresh with the new data (the cells are = the querry table cells).
'I therefore want to pull the data out of the cells and paste it to a new/the next row on the briefing page before the query refreshes on the next URL.
'made MR excel account to inquire

    
Sheets("UFD Manipulation").Range("E20").Copy
Sheets("AM INTL Briefing").Range.Cells(i, 7).PasteSpecial xlPasteValues

Sheets("UFD Manipulation").Range("E21").Copy
Sheets("AM INTL Briefing").Range.Cells(i, 8).PasteSpecial xlPasteValues
    
    
Next i

End Sub

 

[Kc7487]

Hi again all, I'm think I made that too wordy so I want to make my question more simple here, I can edit it myself given the right start....

I have a column of URLs in column A of "Sheet1". I have a web Query on "Sheet2". I want a macro to loop through all URLs in column A of "Sheet1", changing the WebQuery to use the URL as it's connection, copy cell A1 of the web query on "Sheet2", and paste that value to column B of "Sheet1" right next to the URL it is coming from.

Hopefully that is a clearer proposition, any idea how I might go about getting this done?