Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Do you want to replace the contents of the destination cells?" | Excel Discussion (Misc queries) | |||
How do I suppress the "Do you want to overwrite the destination cells" message | Setting up and Configuration of Excel | |||
"Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
Paste in "match destination format" macro code | Excel Discussion (Misc queries) |