J
JB
Hello,
I have a macro that retrieves data from Yahoo (Sub quotesColK())
and it works fine. I then run Sub ColKExpK() and this also works
fine. But, when I now run Sub quotesColK() ( after having run Sub
ColKExpK()) I get the message "Do you want to replace the contents of
the destination cells" and if I respond yes, the "Label Rows (rows 1
& 2) shift to the right one cell and the data does not line up
correctly. What changes in the code, to either macro, do I need to
make so that this "shift" does not happen?
Thanks.
JBESr
Sub quotesColK()
Application.ScreenUpdating = False
Sheets("QuoteColKData").Range("$A$3:$P$300").Delete
Sheets("Quotes").Activate
x = Sheets("Quotes").Range("k" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Quotes").Range(Cells(5, 11), Cells(x, 11))
symbols = symbols & "+" & c
Next
With Sheets("QuoteColKData").QueryTables.Add(Connection:="URL;" _
& "http://finance.yahoo.com/d/quotes.csv?s=" & _
symbols & "&f=snd1t1l1bc1ohgpvd1at8&e=.csv", _
Destination:=Sheets("QuoteColKData").Range("b3"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Sheets("QuoteColKData").Select
With Sheets("QuoteColKData")
..[b3:b300].TextToColumns
Destination:=Sheets("QuoteColKData").Range("b3"),
DataType:=xlDelimited, Comma:=True
..Columns("A:X").EntireColumn.AutoFit
End With
Calculate
Application.ScreenUpdating = True
End Sub
Sub ColKExpK()
'
' ColKExpK Macro
' Macro recorded 10/16/2004 by End User
'
Application.ScreenUpdating = False
Sheets("QuoteColKData").Select
Range("Q1:R1").Select
Selection.Copy
Range("Q3:Q108").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
End Sub
I have a macro that retrieves data from Yahoo (Sub quotesColK())
and it works fine. I then run Sub ColKExpK() and this also works
fine. But, when I now run Sub quotesColK() ( after having run Sub
ColKExpK()) I get the message "Do you want to replace the contents of
the destination cells" and if I respond yes, the "Label Rows (rows 1
& 2) shift to the right one cell and the data does not line up
correctly. What changes in the code, to either macro, do I need to
make so that this "shift" does not happen?
Thanks.
JBESr
Sub quotesColK()
Application.ScreenUpdating = False
Sheets("QuoteColKData").Range("$A$3:$P$300").Delete
Sheets("Quotes").Activate
x = Sheets("Quotes").Range("k" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Quotes").Range(Cells(5, 11), Cells(x, 11))
symbols = symbols & "+" & c
Next
With Sheets("QuoteColKData").QueryTables.Add(Connection:="URL;" _
& "http://finance.yahoo.com/d/quotes.csv?s=" & _
symbols & "&f=snd1t1l1bc1ohgpvd1at8&e=.csv", _
Destination:=Sheets("QuoteColKData").Range("b3"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Sheets("QuoteColKData").Select
With Sheets("QuoteColKData")
..[b3:b300].TextToColumns
Destination:=Sheets("QuoteColKData").Range("b3"),
DataType:=xlDelimited, Comma:=True
..Columns("A:X").EntireColumn.AutoFit
End With
Calculate
Application.ScreenUpdating = True
End Sub
Sub ColKExpK()
'
' ColKExpK Macro
' Macro recorded 10/16/2004 by End User
'
Application.ScreenUpdating = False
Sheets("QuoteColKData").Select
Range("Q1:R1").Select
Selection.Copy
Range("Q3:Q108").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
End Sub