I changed your variable names slightly. The key code change is you need to
make the sheet that you are adding the QueryTable to the ActiveSheet.

Sheets(ii).Activate '''<== sheet must be the active sheet.

Troy


Sub WebQuery_on_SheetName()
'Web Query for based on the name of each sheet
'I want the info based on the sheet name to be placed on the
'appropriate sheet.

Dim sName As String
Dim iCount As Integer
Dim ii As Integer

iCount = Sheets.Count
For ii = 1 To iCount

sName = Sheets(ii).Name

Sheets(ii).Activate '''<== sheet must be the active sheet.

With Sheets(ii).QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?d=t&s=" & sName,
Destination:=Range("A1"))
.Name = "q?d=t&s=" & sName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "24,26"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Next ii

End Sub


"Btibert " wrote in message
...
Hi guys.

I wrote this quote such that I was hoping that I would be able to do a
web query based on the name of each worksheet in a particular workbook.
Here is the code:


-----------------------

Sub WebQuery_on_SheetName()
'
'Web Query for based on the name of each sheet
'
'I want the info based on the sheet name to be placed on the
'appropriate sheet.




Dim sname As String
Dim scount As Integer
scount = Sheets.Count


For i = 1 To scount

sname = Sheets(i).Name


With Sheets(i).QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?d=t&s=" & sname,
Destination:=Range("A1"))
Name = "q?d=t&s=" & sname
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = False
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = False
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
WebSelectionType = xlSpecifiedTables
WebFormatting = xlWebFormattingAll
WebTables = "24,26"
WebPreFormattedTextToColumns = True
WebConsecutiveDelimitersAsOne = True
WebSingleBlockTextImport = False
WebDisableDateRecognition = False
Refresh BackgroundQuery:=False
End With

Next i



End Sub

----------------

The error that i am recieving when I attemp the macro is that the
Destination range is not on the same worksheet that the Query table is
being created on.

I am new to VBA and am attempting to teach it to myself. First, my
major assumption is that excel will let me do a query on each
worksheet. From there, how can I get excel to perform a web query such
that the data is placed on the appropriate worksheet?

Any help will be greatly appreciated.

Thanks,

Brock


---
Message posted from http://www.ExcelForum.com/