VBA Paste Loop Question

[JXI115]

Hello, I am currently using the following code to pull in data from various websites.

Sub Macro3()
'
' Macro3 Macro
'
'

Dim Erw, Frw, Lrw
Frw = 1
Lrw = Range("A" & Rows.Count).End(xlUp).Row
For Erw = Frw To Lrw


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("A" & Erw).Value, Destination:=Range("B" & Erw))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = x1InsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next Erw


End Sub

I have all web address' on column A, and the code looks at each link then pulls the 20 rows of data I need from the websites.
ex.
Stock Screener - Custom
Stock Screener - Custom

The problem I have is that the code will paste into cell B1, then paste into cell B2 etc. Considering the data I am importing is 20 rows long, I need the code to paste to every 20 rows in column B.

I can't figure out how to do that.

Thanks for anyone that can help.

 

[MARK858]

Can you not just do

For Erw = Frw To Lrw Step 20

?

 

[JXI115]

Thanks for your help. I just tried it but by adding step 20; now it only pulls from the first website link.

 

[JXI115]

According to research I have done Step 20 does sound like it would be the correct thing to add. Is there anywhere else I could add it where it might work?Thanks

 

[MARK858]

Why would you want to add it anywhere else where you have the For statement is the correct place.

Btw

Dim Erw, Frw, Lrw

should be

Dim Erw As Long, Frw As Long, Lrw As Long

 

[JXI115]

Oh! Sorry about that. I was just guessing if it would work anywhere else. I corrected the first part of the code and added "As Long". I honestly cannot figure out what to do to get this code to work.

 

[MARK858]

figure out what to do to get this code to work.

Post the code in the thread with the amendments you have made (please put it in code tags as well. Just paste the code in the thread then select all the code and click the # icon).

 

[JXI115]

I haven't made any amendments. I am completely stuck. I am very new to VBA and I cannot even think of what else I could do to solve the problem.

 

[MARK858]

I haven't made any amendments.

You obviously have made amendments because you wrote...

I corrected the first part of the code and added "As Long".

Your code should look like

Sub Macro3()

    Dim Erw As Long, Frw As Long, Lrw As Long
    Frw = 1
    Lrw = Range("A" & Rows.Count).End(xlUp).Row

    For Erw = Frw To Lrw Step 20

        With ActiveSheet.QueryTables.Add(Connection:= _
                                         "URL;" & Range("A" & Erw).Value, Destination:=Range("B" & Erw))
            .Name = ""
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = x1InsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "10"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With

    Next Erw

End Sub

or more likely...

Sub Macro3b()

    Dim Erw As Long, Frw As Long, Lrw As Long, Erw1 As Long
    Frw = 1
    Erw1 = 1
    Lrw = Range("A" & Rows.Count).End(xlUp).Row

    For Erw = Frw To Lrw

        With ActiveSheet.QueryTables.Add(Connection:= _
                                         "URL;" & Range("A" & Erw).Value, Destination:=Range("B" & Erw1))
            .Name = ""
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = x1InsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "10"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Erw1 = Erw1 + 20
    Next Erw

End Sub