Macro causes "Do you want to replace the contents of the destination cells"
 

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").Delet e
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

Macro causes "Do you want to replace the contents of the destination cells"
 

change
Sheets("QuoteColKData").Range("$A$3:$P$300").Delet e
to
Sheets("QuoteColKData").Range("$A$2:$R$300").Delet e

--
Regards,
Tom Ogilvy

"JB" wrote in message
om...
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").Delet e
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