Error 1004: Select method of Range class failed

[VicM]

I'm attempting to modify a spreadsheet from Google finance which captures historical stock quotes. One enters the stock symbol and dates on the first sheet then activates the button to get the data on the "Data" sheet.
The Google file is: "Google Finance Stock Quotes.xlsm"

The macro uses the querytables function and initially puts the CSV headers in A1 and the CSV stock data in A2. It then converts text to columns, thereby ranging from column A thru column F. Row A1 contains the headers and row A2 contains the data for the 1st stock symbol.

The macro works properly.

However, I'm attempting to modify the action in order to get all my stock quotes for the end of a given month with just the push of one button.
I first created a "Symbols" sheet listing all the symbols I'm interested in. I want to iterate thru the symbols keeping the same date so I can build a table on the "Data" sheet to eventually be imported into my Quicken app.
So instead of having to type the symbol in the first sheet, I only need to enter the end of month date. Then I can iterate thru the symbols.
What happens after the first data is retrieved is that the second querytable action adds the next symbol's data in a newly created A column in row 3, thereby shifting the first set of data one column right.
What I attempt to do is copy the new data (2 rows, 1 column worth) and move it to column B. That way I can delete the newly formed column A. Then I would want to delete row 3 which now contains the CSV for column headers again. And lastly I would convert the new row 3, (which now would contain the CSV data for the next stock symbol), to columns to that the new data lines up under the proper headers.
The problem arises when I attempt to select the range of A1:A2; I get error 1004: Select method of range class failed.

I've used breakpoints and Debug.Print to follow the code, but it always fails at this point.

Option Explicit

Sub GetData()
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim i As Integer, j As Integer
Dim r1 As String
Dim QTCnt As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Sheets("Data").Cells.Clear

Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("startDate").Value
EndDate = DataSheet.Range("endDate").Value
'[COLOR=#4E9A06]Symbol = DataSheet.Range("ticker").Value  THIS LINE IS REPLACED WITH THE FOR LOOP FOLLOWING[/color]
Sheets("Data").Range("a1").CurrentRegion.ClearContents

For i = 1 To 82 [COLOR=#4E9A06]'There are 82 stock symbols on the Sheets("Symbols")[/color]

Symbol = Trim(Sheets("Symbols").Range("a" & i).Value)

qurl = "[URL unfurl="true"]http://finance.google.com/finance/historical?q="[/URL] & Symbol
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
       "+" & Day(StartDate) & "+" & Year(StartDate) & _
       "&enddate=" & MonthName(Month(EndDate), True) & _
       "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"

QueryQuote:
[COLOR=#4E9A06]'First time thru works as planned[/color]
If i = 1 Then   [COLOR=#4E9A06]'The section containing the WITH statement was in the original Google Finance .xlsm
                'I moved the Refresh statement after the SaveData statement[/color]

    With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _
[indent][/indent][indent][/indent]Destination:=Sheets("Data").Range("a" & i))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        [COLOR=#4E9A06]'.Refresh BackgroundQuery:=False[/color]
        .SaveData = True
        .Refresh BackgroundQuery:=False
    End With
    
    QTCnt = Sheets("Data").QueryTables.Count
    
    Debug.Print QTCnt
    
    For j = 1 To QTCnt
    
    Sheets("Data").QueryTables.Item(j).Delete
    
    Next j
    
    
    Sheets("Data").Range("a" & i).TextToColumns Destination:=Sheets("Data").Range("a" & i), _
[indent]DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _[/indent]
        Semicolon:=False, Comma:=True, Space:=False, other:=False
                                                           
Sheets("Data").Range("a" & i + 1).TextToColumns Destination:=Sheets("Data").Range("a" & i + 1), _
[indent]DataType:=xlDelimited, _[/indent]
[indent]TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _[/indent]
[indent]Semicolon:=False, Comma:=True, Space:=False, other:=False[/indent]
    
Else  [COLOR=#4E9A06]'Subsequent times thru the loop come here[/color]

    With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _
[indent]Destination:=Sheets("Data").Range("a" & i + 1))[/indent]
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        [COLOR=#4E9A06]'.Refresh BackgroundQuery:=False[/color]
        .SaveData = True
        .Refresh BackgroundQuery:=False
        
    End With
    
    QTCnt = Sheets("Data").QueryTables.Count
    
    Debug.Print QTCnt
    
    
    For j = 1 To QTCnt
    
    Sheets("Data").QueryTables.Item(j).Delete
    
    Next j
    
    r1 = "A" & i + 1 & ":A" & i + 2
    
    Debug.Print r1
    
    Sheets("Data").Range(r1).Select  [COLOR=#4E9A06][b]'THIS IS WHERE THE CODE FAILS!!!![/b][/color]
    
    Sheets("Data").Selection.Copy
    Sheets("Data").Range("B" & i + 1).Select
    Application.CutCopyMode = False
    Sheets("Data").Columns("A:A").Select
    Sheets("Data").Selection.Delete Shift:=xlToLeft
    Sheets("Data").Range("A" & i + 1).Select
    
    Sheets("Data").Rows(i + 1).Delete
    
Sheets("Data").Range("a" & i + 1).TextToColumns Destination:=Sheets("Data").Range("a" & i + 1), _
[indent]DataType:=xlDelimited, _[/indent]
[indent]TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _[/indent]
[indent]Semicolon:=False, Comma:=True, Space:=False, other:=False[/indent]

End If

Sheets("Data").Range("G" & i + 1) = Symbol

Next i

Sheets("Data").Columns("A:G").ColumnWidth = 12


End Sub

Any insights would be greatly welcomed.
Thanks,
Vic

 

[SkipVought]

Hi,

Avoid Select and Activate....

.
    Sheets("Data").Range(r1).Copy

If you do use the Select method, be certain that the sheet in question is the ActiveSheet

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

Thanks Skip. I'll give that a try.